?
Solved

Conditional Select Statement in DB2

Posted on 2011-10-03
19
Medium Priority
?
649 Views
Last Modified: 2012-06-21
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



0
Comment
Question by:pvsbandi
  • 8
  • 5
  • 5
18 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1336 total points
ID: 36905616
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;
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 36905632
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

0
 

Author Comment

by:pvsbandi
ID: 36905714
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.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 36905775
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
0
 

Author Comment

by:pvsbandi
ID: 36905810
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

0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 1336 total points
ID: 36905880
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 *
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 36905969
Hi pvsbandi,

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

0
 

Author Comment

by:pvsbandi
ID: 36905981
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

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36905985
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
0
 

Author Comment

by:pvsbandi
ID: 36906006
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.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36906073
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
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 664 total points
ID: 36906078
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

0
 

Author Comment

by:pvsbandi
ID: 36906085
Great! Thank you!
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 36906114
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

0
 

Author Comment

by:pvsbandi
ID: 36907148
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?
 
0
 

Author Comment

by:pvsbandi
ID: 36907253
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.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 36907330
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
0
 

Author Closing Comment

by:pvsbandi
ID: 36913586
Thanks very much!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question