Einzelnen Beitrag anzeigen

Blackpit

Registriert seit: 27. Feb 2019
43 Beiträge
 
#21

AW: Strukturänderung Ja/Nein-Felder in Firebird

  Alt 9. Sep 2019, 19:58
Hallo,
hier beide komplett:

GETALLDETAILS
Code:
SET TERM ^ ;

create or alter procedure GETALLDETAILS
returns (
    MYID integer,
    TABLE_NAME varchar(100) character set UTF8,
    COLUMN_NAME varchar(100) character set UTF8,
    COLUMN_VALUE char(100) character set UTF8)
as
begin
  for select COMP_ID
      from COMP_AUS
      group by COMP_ID
      into :MYID
  do
  begin
    for select F.RDB$RELATION_NAME, F.RDB$FIELD_NAME
        from RDB$RELATION_FIELDS F
        join RDB$RELATIONS R on F.RDB$RELATION_NAME = R.RDB$RELATION_NAME and R.RDB$VIEW_BLR is null and (R.RDB$SYSTEM_FLAG is null or R.RDB$SYSTEM_FLAG = 0)
        where (upper(F.RDB$RELATION_NAME) starting with upper('COMP_AUS')) and
              (upper(F.RDB$FIELD_NAME) containing upper('_JN'))
        order by 1, F.RDB$FIELD_POSITION
        into :TABLE_NAME, :COLUMN_NAME
    do
    begin
      execute statement 'select cast(' || COLUMN_NAME || ' as varchar(100)) from ' || TABLE_NAME || ' where COMP_ID =' || MYID
          into :COLUMN_VALUE;
      suspend;
    end
  end
end^

SET TERM ; ^

GRANT SELECT ON COMP_AUS TO PROCEDURE GETALLDETAILS;

GRANT EXECUTE ON PROCEDURE GETALLDETAILS TO MYDB;
GETCOMPDETAILS
Code:
SET TERM ^ ;

create or alter procedure GETCOMPDETAILS
as
declare variable DET_ID integer;
declare variable BEZEICH varchar(100) character set UTF8;
declare variable MYID integer;
declare variable COLUMN_NAME varchar(50) character set UTF8;
  begin
    for select myID, column_name
        from GETALLDETAILS
        where UPPER(COLUMN_VALUE) = UPPER('j')
        into myid, column_name
    do
    begin
    if (column_name not CONTAINING 'xxx') then
    begin
    select Bezeichnung from dmchange where Field_Name = :column_name into :Bezeich;
    select AUS_DET_ID from comp_aus_detail where Bezeichnung = :Bezeich into :DET_ID;
    if (bezeich is null and det_id is null) then
    begin
    Insert into comp_aus_detail (bezeichnung) Values (:Bezeich) returning aus_det_id into :det_id;/*return gen_ID*/
    suspend;
    end
    if (det_id is not null) then
    insert into comp_kat_details (KAT_ID,DET_ID,KID_ID) values (1,:DET_ID,:myid);
    end
    end
  end;^

SET TERM ; ^

GRANT EXECUTE ON PROCEDURE GETALLDETAILS TO PROCEDURE GETCOMPDETAILS;

GRANT SELECT ON DMCHANGE TO PROCEDURE GETCOMPDETAILS;

GRANT SELECT,INSERT ON COMP_AUS_DETAIL TO PROCEDURE GETCOMPDETAILS;

GRANT INSERT ON COMP_KAT_DETAILS TO PROCEDURE GETCOMPDETAILS;

GRANT EXECUTE ON PROCEDURE GETCOMPDETAILS TO MYDB;
Erste Proc allein liefert wie gewünscht.
Im Einzelschritt läüft GETCOMPDETAILS auch wie es soll.
Wenn ich es durchlaufen lasse nicht mehr
Ich dachte erst an Feldnamendoppellungen welche auch existieren, erklären kann ich es mir nicht.

Geändert von Blackpit ( 9. Sep 2019 um 20:02 Uhr)
  Mit Zitat antworten Zitat