Link to home
Start Free TrialLog in
Avatar of pvsbandi
pvsbandiFlag for United States of America

asked on

change the select dynamically

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

ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pvsbandi

ASKER

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