Loop or Cursor in Navigator SQL

[MFisher]

I want to loop through a table, update a CHAR(30) variable, and write records to another table based on the variable.
This is in "interactive" Navigator session, not a program.
I can't seem to declare a cursor interactively. Is it possible ?

So, instead, I could use a GOTO type loop and add 1 to counter, then exit the loop when the counter hits a value, but hit roadblocks too.

I am used to Microsoft SQL, and keep hitting roadblocks trying to do the same thing in "Navigator SQL"

[B.Hauser]

You cannot define a cursor or goto in interactive SQL.
You have to embedd it in a stored procedure, user defined function ... or at least in a dynamic compound statement (BEGIN ... END

Birgitta

[TedHolt]

What Birgitta says is spot on (of course). You can use a dynamic compound statement. Here's a simple example.

declare global temporary table numbers
    ( seq   integer);

begin
   declare v_counter  integer  default 0;

   while v_counter < 5 do
      set v_counter = v_counter + 1;
      insert into session.numbers values(v_counter);
   end while;

end;

select * from session.numbers;

[TedHolt]

Here's another example. This one has a dynamic compound statement with a cursor in it.

declare global temporary table names
  ( LastName   char(12), initials char(3));

begin

   declare  v_Last    char(8);
   declare  v_Init    char(3);
   declare  SQLState  char(5);

   declare NameTest cursor for
      select lstnam, init
        from qiws.qcustcdt
       where substr(init, 3, 1) <= 'L'
       order by lstnam;

   open NameTest;

FetchLoop:
   loop
      fetch NameTest into v_Last, v_Init;
      if SQLState >= '02000' then
         leave FetchLoop;
      end if;
      insert into session.names values (v_Last, v_Init);
   end loop;

   close NameTest;

end;

select * from session.names;

The FOR loop is a shortcut for a cursor.

begin

   for NameTest as
      select lstnam, init
        from qiws.qcustcdt
       where substr(init, 3, 1) <= 'L'
       order by lstnam
   do
      insert into session.names values (NameTest.lstnam, NameTest.init);   
   end for;

end;

[MFisher]

Here's another example. This one has a dynamic compound statement with a cursor in it.

declare global temporary table names
( LastName char(12), initials char(3));

begin

declare v_Last char(8);
declare v_Init char(3);
declare SQLState char(5);

declare NameTest cursor for
select lstnam, init
from qiws.qcustcdt
where substr(init, 3, 1) <= 'L'
order by lstnam;

open NameTest;

FetchLoop:
loop
fetch NameTest into v_Last, v_Init;
if SQLState >= '02000' then
leave FetchLoop;
end if;
insert into session.names values (v_Last, v_Init);
end loop;

close NameTest;

end;

select * from session.names;

The FOR loop is a shortcut for a cursor.

begin

for NameTest as
select lstnam, init
from qiws.qcustcdt
where substr(init, 3, 1) <= 'L'
order by lstnam
do
insert into session.names values (NameTest.lstnam, NameTest.init);
end for;

end;

I tried a version of your code, but getting an error. I think I am populating the variables v_Lib , v_Table , v_Member from the cursor.
But when I create the alias, It is using the literal "v_Lib" , not the value in v_Lib.
What am I missing ?

SQL State: 42704
Vendor Code: -204
Message: [SQL0204] V_TABLE in V_LIB type *FILE not found. Cause . . . .

begin

   declare  v_Lib    char(50);
   declare  v_Table    char(50);
declare v_member char(50);
   declare  SQLState  char(5);

   declare SourceCode cursor for
      select Table_Schema , Table_Name , Table_Partition
        FROM GABRIEL.CL_SOURCE
;

   open SourceCode ;

FetchLoop:
   loop
      fetch SourceCode into v_Lib , v_Table,  v_member  ;
      if SQLState >= '02000' then
         leave FetchLoop;
      end if;
CREATE ALIAS QTEMP.MYMBRB FOR v_Lib.v_Table (v_member) ;
  INSERT INTO GABRIEL.SRC_SEARCH  SELECT SRCSEQ , SRCDTA , 'Created'  FROM QTEMP.MYMBRB where SRCDTA like '%Created%'  ;
DROP ALIAS QTEMP.MYMBRB ;
   end loop;

   close SourceCode;

end;

[MFisher]

Here's another example. This one has a dynamic compound statement with a cursor in it.

declare global temporary table names
( LastName char(12), initials char(3));

begin

declare v_Last char(8);
declare v_Init char(3);
declare SQLState char(5);

declare NameTest cursor for
select lstnam, init
from qiws.qcustcdt
where substr(init, 3, 1) <= 'L'
order by lstnam;

open NameTest;

FetchLoop:
loop
fetch NameTest into v_Last, v_Init;
if SQLState >= '02000' then
leave FetchLoop;
end if;
insert into session.names values (v_Last, v_Init);
end loop;

close NameTest;

end;

select * from session.names;

The FOR loop is a shortcut for a cursor.

begin

for NameTest as
select lstnam, init
from qiws.qcustcdt
where substr(init, 3, 1) <= 'L'
order by lstnam
do
insert into session.names values (NameTest.lstnam, NameTest.init);
end for;

end;

I tried a version of your code, but getting an error. I think I am populating the variables v_Lib , v_Table , v_Member from the cursor.
But when I create the alias, It is using the literal "v_Lib" , not the value in v_Lib.


SQL State: 42704
Vendor Code: -204
Message: [SQL0204] V_TABLE in V_LIB type *FILE not found. Cause . . . .

begin

   declare  v_Lib    char(50);
   declare  v_Table    char(50);
declare v_member char(50);
   declare  SQLState  char(5);

   declare SourceCode cursor for
      select Table_Schema , Table_Name , Table_Partition
        FROM GABRIEL.CL_SOURCE
;

   open SourceCode ;

FetchLoop:
   loop
      fetch SourceCode into v_Lib , v_Table,  v_member  ;
      if SQLState >= '02000' then
         leave FetchLoop;
      end if;
CREATE ALIAS QTEMP.MYMBRB FOR v_Lib.v_Table (v_member) ;
  INSERT INTO GABRIEL.SRC_SEARCH  SELECT SRCSEQ , SRCDTA , 'Created'  FROM QTEMP.MYMBRB where SRCDTA like '%Created%'  ;
DROP ALIAS QTEMP.MYMBRB ;
   end loop;

   close SourceCode;

end;

[MFisher]

Can someone please "Approve" one of my replies in this thread so it's visible to others ?
P.S. I am not a spammer, so not sure why they are "Unapproved".

[MFisher]

Ted Holt:

I tried a version of your code, but getting an error. I think I am populating the variables v_Lib , v_Table , v_Member from the cursor.

But when I create the alias, It is using the literal "v_Lib" , not the value in v_Lib.

SQL State: 42704

Vendor Code: -204

Message: [SQL0204] V_TABLE in V_LIB type *FILE not found. Cause . . . .

begin declare v_Lib char(50);
declare v_Table char(50);
declare v_member char(50);
declare SQLState char(5);
declare SourceCode cursor for select Table_Schema , Table_Name , Table_Partition FROM MyLib.CL_SOURCE ;

open SourceCode ;
FetchLoop:
loop
fetch SourceCode into v_Lib , v_Table, v_member ;
if SQLState >= '02000'
then leave FetchLoop;
end if;

CREATE ALIAS QTEMP.MYMBRB FOR v_Lib.v_Table (v_member) ;

INSERT INTO MyLib.SRC_SEARCH SELECT SRCSEQ , SRCDTA , 'SearchString' FROM QTEMP.MYMBRB where SRCDTA like '%SearchString%' ;

DROP ALIAS QTEMP.MYMBRB ;

end loop;
close SourceCode;
end;

Modified post to try & get around the posting bug.

[MFisher]

Ted Holt:

I tried a version of your code, but getting an error. I think I am populating the variables v_Lib , v_Table , v_Member from the cursor.

But when I create the alias, It is using the literal "v_Lib" , not the value in v_Lib.



SQL State: 42704

Vendor Code: -204

Message: [SQL0204] V_TABLE in V_LIB type *FILE not found. Cause . . . .

begin
declare v_Lib char(50);
declare v_Table char(50);
declare v_member char(50);
declare SQLState char(5);
declare SourceCode cursor for
select Table_Schema , Table_Name , Table_Partition FROM MyLib.CL_SOURCE ;

open SourceCode ;

FetchLoop:
loop fetch SourceCode into v_Lib , v_Table, v_member ;
if SQLState >= '02000' then leave FetchLoop;
end if;
CREATE ALIAS QTEMP.MYMBRB FOR v_Lib.v_Table (v_member) ;
INSERT INTO MyLib.SRC_SEARCH SELECT SRCSEQ , SRCDTA , 'SearchString' FROM QTEMP.MYMBRB where SRCDTA like '%SearchString%' ;

DROP ALIAS QTEMP.MYMBRB ;

end loop;
close SourceCode;
end;

Modified 4th post to try & get around the posting bug. (I am not a spammer....jeeeesh)

[TedHolt]

For whatever reason, CREATE ALIAS takes those strings literally. You could use EXECUTE IMMEDIATELY.

begin
   declare v_Lib       char(10)   default 'MYLIB';
   declare v_Table     char(10)   default 'MYFILE';
   declare v_Member    char(10)   default 'TWO';
   declare v_Cmd    varchar(256);

   set v_Cmd = 'CREATE ALIAS QTEMP.MYMBRB FOR ' concat
                v_Lib concat '.' concat v_Table concat ' (' concat  v_member concat ')';

   execute immediate v_Cmd;
end;

[MFisher]

Thank you, I will try that later when not so busy with other projects.

[B.Hauser]

For whatever reason, CREATE ALIAS takes those strings literally. You could use EXECUTE IMMEDIATELY.

Schema and file names cannot be handled as Host Variables. For dealing with varying schemas and/or files you have to use dynamic SQL

[MFisher]

For whatever reason, CREATE ALIAS takes those strings literally. You could use EXECUTE IMMEDIATELY.

begin
declare v_Lib char(10) default 'MYLIB';
declare v_Table char(10) default 'MYFILE';
declare v_Member char(10) default 'TWO';
declare v_Cmd varchar(256);

set v_Cmd = 'CREATE ALIAS QTEMP.MYMBRB FOR ' concat
v_Lib concat '.' concat v_Table concat ' (' concat v_member concat ')';

execute immediate v_Cmd;
end;

THANKS !
That worked