Conditional Select Statement in DB2

Hi,

  I was trying something like this, but it errs out. How to conditionally execute a select statement? Please help.

CREATE PROCEDURE RE39r (IN V_ID INTEGER, IN V_TYPE CHAR(2))
SPECIFIC RE39r

 DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
IF V_TYPE = 'CH' THEN 

select * from abc
where id = V_ID;

elseif V_TYPE = 'OM' THEN

SELECT * FROM DEF
WHERE ID = V_ID;

ELSEIF V_TYPE = 'RI' THEN

SELECT * FROM KLM
WHERE ID = V_ID;

END IF;

OPEN CUR1;

END P1

Open in new window



pvsbandiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
you can't do it like that
either create 3 cursors, or use something like



 DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
select * from abc
where id = V_ID
and 'CH' = ?
union all
SELECT * FROM DEF
WHERE ID = V_ID
and 'OM' = ?
union all
SELECT * FROM KLM
WHERE ID = V_ID
and 'RI' = ?
;

OPEN CUR1 using v_type, v_type, v_type;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kent OlsenDBACommented:
Hi pvsbandi,

I believe that you need to identify the procedure as SQL, include a label, and a BEGIN marker, for starters.


Kent

CREATE PROCEDURE RE39r (IN V_ID INTEGER, IN V_TYPE CHAR(2))
SPECIFIC RE39r
LANGUAGE SQL
P1:
BEGIN

 DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
IF V_TYPE = 'CH' THEN 

select * from abc
where id = V_ID;

elseif V_TYPE = 'OM' THEN

SELECT * FROM DEF
WHERE ID = V_ID;

ELSEIF V_TYPE = 'RI' THEN

SELECT * FROM KLM
WHERE ID = V_ID;

END IF;

OPEN CUR1;

END P1

Open in new window

pvsbandiAuthor Commented:
Hi Momi,

    The columns returned, as not identical across the select statements; so, i can't union them.

Hi Kent,

     I've added Language SQL. P1: Begin and End are there.
 But it didn't work.

Please help.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

momi_sabagCommented:
you can't have an if inside a cursor
you will have to use 3 different cursors, or alternatively, prepare the cursor from a dynamic generated sql statement using a string variable
pvsbandiAuthor Commented:
I tried something like this. But it didn't like it either.
CREATE PROCEDURE RE39r (IN V_ID INTEGER, IN V_TYPE CHAR(2))
SPECIFIC RE39r
LANGUAGE SQL

P1:BEGIN
IF V_TYPE = 'CH' THEN 
 DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
select * from abc
where id = V_ID;
OPEN CUR1;

elseif V_TYPE = 'OM' THEN
DECLARE CUR2 CURSOR WITH RETURN TO CALLER FOR
SELECT * FROM DEF
WHERE ID = V_ID;
OPEN CUR2;
ELSEIF V_TYPE = 'RI' THEN
DECLARE CUR3 CURSOR WITH RETURN TO CALLER FOR
SELECT * FROM KLM
WHERE ID = V_ID;
OPEN CUR3;
END IF;

END P1

Open in new window

momi_sabagCommented:
that is because all declare cursor statements should go on top

what does the rest of the code do?
why not use the UNION option but without the *
Kent OlsenDBACommented:
Hi pvsbandi,

Declare all 3 cursors at the top, and open/process the one you need within the IF block.

pvsbandiAuthor Commented:
Thanks Momi! I tried the following. Seems to work,, Can you please see if this has any issues?

CREATE PROCEDURE RE39r (IN V_ID INTEGER, IN V_TYPE CHAR(2))
SPECIFIC RE39r
LANGUAGE SQL

P1:BEGIN

 DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
select * from abc
where id = V_ID;

DECLARE CUR2 CURSOR WITH RETURN TO CALLER FOR
SELECT * FROM DEF
WHERE ID = V_ID;

DECLARE CUR3 CURSOR WITH RETURN TO CALLER FOR
SELECT * FROM KLM
WHERE ID = V_ID;

IF V_TYPE = 'CH' THEN
OPEN CUR1;
ELSEIF V_TYPE = 'OM' THEN
OPEN CUR2;
ELSEIF V_TYPE = 'RI' THEN
OPEN CUR3;
END IF;

END P1

Open in new window

momi_sabagCommented:
this is great
the only problem is that you will have to use your if conditions when you need to fetch from the cursor and when you need to close it
pvsbandiAuthor Commented:
Do i need to close the cursors? I'm using them for reporting purposes.
  This stored procedure will be called from Crystal Reports to display the data based on the parameters.
momi_sabagCommented:
no
since the cursor is returned to the caller, no need to close it
actually
no need to fetch either
my mistake

you will have to know which cursor to return though
Kent OlsenDBACommented:
Hi pvsbandi,

Going back to the original question, you can create procedures that will open your cursors and return them back to this procedure.  That will allow you to keep the same name for the cursor throughout this procedure.  A quick example (non-working) is below.

Personally, I wouldn't do it for such a simple procedure.  But in a large, complex model it might make some sense to do it this way.



Kent

CREATE PROCEDURE RE39r (IN V_ID INTEGER, IN V_TYPE CHAR(2))
SPECIFIC RE39r
LANGUAGE SQL

P1:BEGIN

IF V_TYPE = 'CH' THEN
  CALL OPEN_RE39r_CUR1;
ELSEIF V_TYPE = 'OM' THEN
  CALL OPEN_RE39r_CUR2;
ELSEIF V_TYPE = 'RI' THEN
  CALL OPEN_RE39r_CUR3;
END IF;

FETCH .. CUR1 ..;

END P1;

------------------------------------------

CREATE PROCEDURE OPEN_RE39r_CUR1
LANGAUGE SQL
DYNAMIC RESULT SETS 1
P1:
BEGIN

DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
  select * from abc;

RETURN;
END;

------------------------------------------

CREATE PROCEDURE OPEN_RE39r_CUR2
LANGAUGE SQL
DYNAMIC RESULT SETS 1
P1:
BEGIN

DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
  select * from def;

RETURN;
END;

------------------------------------------

CREATE PROCEDURE OPEN_RE39r_CUR3
LANGAUGE SQL
DYNAMIC RESULT SETS 1
P1:
BEGIN

DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR
  select * from ghi;

RETURN;
END;

Open in new window

pvsbandiAuthor Commented:
Great! Thank you!
Kent OlsenDBACommented:
Hi pvsbandi,

I don't usually question the assignment of points, but Momi provided as much or more information than I did.  Anyone looking that this thread later will likely get a lot more out of it from the other comments than the one that you accepted.

I think that it's appropriate to rescore this.


Kent

pvsbandiAuthor Commented:
Hi Kent,

   The key in Momi's comment was "Declare all 3 cursors at the top", which led me to declare the cursors on top and find the way out.

     But honestly, i didn't look at your solution. but looks like your solution is very nice.
     Is there a way to re-allocate points?
 
pvsbandiAuthor Commented:
I see what happened. Momi had suggested that the cursors need to go on top. Immediately, your comment followed conveying the same thing. I honestly didn't notice your comments; neither 36905969 nor 36906078.

  My intention was to give momi, the points and when i looked at the comment which gave me the idea, found your comments and thought it was Momi's.

  But both of your comments added so much value to this thread, it would be best to give you both, 250 each. If you have the ability to do so, please do the needful.
Kent OlsenDBACommented:
Hi pvsbandi,

Do you see a "request attention" link in the original question?  If so, click on it and ask that the question be reopened so that you can accept the correct answer.


Kent
pvsbandiAuthor Commented:
Thanks very much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.