Avatar of pvsbandi
pvsbandi
Flag 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

DB2

Avatar of undefined
Last Comment
pvsbandi

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
momi_sabag

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck