change the select dynamically

pvsbandi
pvsbandi used Ask the Experts™
on
Hi,

 I have two select statements in my procedure. I have to use them in a for loop dynamically.
 This is how i tried, but didn't work. can someone kindly help get it to work?
CREATE PROCEDURE CHESSIE.PROCEDURE1 (  )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE C1 VARCHAR(10) DEFAULT 'A';
 DECLARE  GLOBAL TEMPORARY TABLE ABC(C_ID INTEGER);

FOR CUR1 AS
CASE WHEN C1 = 'A' THEN
SELECT CLIENT_ID FROM TB_CLIENT
ELSE SELECT CLIENT_ID FROM TB_PROVIDER
END CASE
 DO

INSERT INTO SESSION.ABC
SELECT CUR1.CLIENT_ID;
END FOR;
END P1    

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can't do it
you will have to declare two cursors and the use 2 insert statements in the if
something like

P1: BEGIN
DECLARE C1 VARCHAR(10) DEFAULT 'A';
DECLARE  GLOBAL TEMPORARY TABLE ABC(C_ID INTEGER);

FOR CUR1 AS
SELECT CLIENT_ID FROM TB_CLIENT

FOR CUR2 AS
SELECT CLIENT_ID FROM TB_PROVIDER

if  C1 = 'A' THEN
DO
INSERT INTO SESSION.ABC
SELECT CUR1.CLIENT_ID;
END FOR;

else
DO
INSERT INTO SESSION.ABC
SELECT CUR2.CLIENT_ID;
END FOR;

END P1

Author

Commented:
Thanks! It didn't work because of the way the loops were nested, but gave me an idea to come up with a working solution.
  It is still not clear for me on how to come up with a logic in the case, the two select have different number of columns in the select.
  But, below is what i worked out.


P1: BEGIN
DECLARE C1 VARCHAR(10) DEFAULT 'A';
 DECLARE  GLOBAL TEMPORARY TABLE ABC(C_ID INTEGER);
 DECLARE GLOBAL TEMPORARY TABLE DEF(C_ID INTEGER);

 IF V_C1 = 'A' THEN
 INSERT INTO SESSION.ABC
 SELECT CLIENT_ID FROM TB_CLIENT WHERE CLIENT_ID = 111111;
 ELSE
 INSERT INTO SESSION.ABC
 SELECT CLIENT_ID FROM TB_CLIENT WHERE CLIENT_ID = 7788;
 END IF;

FOR CUR1 AS
SELECT * FROM SESSION.ABC
DO
INSERT INTO SESSION.DEF  VALUES CUR1.C_ID;
END FOR;
P2: BEGIN
DECLARE CUR3 CURSOR WITH RETURN TO CALLER FOR
SELECT * FROM SESSION.DEF;
OPEN CUR3;
END P2;
END P1

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial