ibm db2 if statement

[jtidge]

I'm looking over something stupid here. Can someone tell me how to do an if statement that will do the following. I think this is close, but it's not working. Both sql statements work independently. Something crazy that I'm doing with the IF and definition of cursors I think:

CREATE PROCEDURE SW.ADD_VER_S
(IN pQUAD CHARACTER(2),
IN pSTREET CHARACTER(20),
IN pSTREET_TYPE CHARACTER(4),
IN pADDRESS_NUMBER DECIMAL(5,0))
SPECIFIC SW.ADD_VER_S
RESULT SETS 1
LANGUAGE SQL

IF pSTREET_TYPE = '' THEN


DECLARE cursor1 CURSOR WITH RETURN FOR

Select * from SW.ADDR_VERIFICATION
where QUADRANT = pQUAD
and STREET = pSTREET
and STREET_TYPE between '' and 'ZZZ'
and pADDRESS_NUMBER between LOW_ADDRESS and HIGH_ADDRESS
fetch first 1 row only ;

OPEN cursor1;

else

DECLARE cursor2 CURSOR WITH RETURN FOR

Select * from SW.ADDR_VERIFICATION
where QUADRANT = pQUAD
and STREET = pSTREET
and STREET_TYPE = pSTREET_TYPE
and pADDRESS_NUMBER between LOW_ADDRESS and HIGH_ADDRESS ;

OPEN cursor2;

end if

 

[MarcLodge]

Hi jtidge,
I'm not certain about this, but the examples I have looked at have a 'BEGIN' and an 'END' around the IF statement.

What error are you getting?

Marc

 

[arun010101]

Hi,

I am an amateur in these matters and have been using DB2 for just 1 month, but let me see if my head is worth anything. Why are you defining the cursor within the if-else statement? The declaration can be placed outside, and the cursor is executed and created only when the open command is done, which can be placed in the decision statements.

Just take the declaration out, and that should make your loop a whole lot simpler, and just might clear the problem. Please do tell me if I am mistaken.

Thank you,
Arun

 

[jtidge]

Thanks for the reply Marc.
oops. I forgot to put the begin and end back in. When the BEGIN and END are around the outside of the if statement, I get an error on the second cursor delcaration "Unexpected Cursor Declaration"

I have also tried putting a BEGIN and END around the SQL within each section of the IF-ESLE-END IF. I have used this type of layout in MS SQL. This however produces an error on the BEGIN after the ELSE. "Unexpected Token" error.

-john

 

[jtidge]

Thanks for the reply, Arun. That doesn't seem to fix it. Perhaps my syntax is wrong. Here's what I have :

P1: BEGIN

DECLARE cursor1 CURSOR WITH RETURN FOR

IF pSTREET_TYPE = '' THEN
Select * from SW.ADDR_VERIFICATION
where QUADRANT = pQUAD
and STREET = pSTREET
and STREET_TYPE between '' and 'ZZZ'
and pADDRESS_NUMBER between LOW_ADDRESS and HIGH_ADDRESS
fetch first 1 row only ;

OPEN cursor1;
ELSE
Select * from SW.ADDR_VERIFICATION
where QUADRANT = pQUAD
and STREET = pSTREET
and STREET_TYPE = pSTREET_TYPE
and pADDRESS_NUMBER between LOW_ADDRESS and HIGH_ADDRESS ;

OPEN cursor1;
END IF;
END P1

It doesn't seem top like the DECLARE xxx CURSOR WITH RETURN FOR to be preceding an IF statement. I tried taking out the WITH RETURN FOR but that just caused new errors. Also, I tried moving the OPEN cursor1 outside of the iIF statement, but I still get the same error as before (not liking the RETURN FOR right before the IF.


-john

 

[MarcLodge]

John,
There's an example on the IBM website here:

http://www-3.ibm.com/cgi-bin/db2www...nt.d2w/report?fn=db2v7s0db2s0408.htm#HDRPSMIF

Which might help .....

 

[tzamora]

Try something like this and you won't need the "IF"...

Select * from SW.ADDR_VERIFICATION
where QUADRANT = pQUAD
and STREET = pSTREET
and pADDRESS_NUMBER between LOW_ADDRESS and HIGH_ADDRESS
and STREET_TYPE LIKE (CASE WHEN pSTREET_TYPE IS NULL
THEN '''%' ELSE '''|pSTREET_TYPE|'%'|''' END)
;

http://programmer-by-the-hour.com

 

[jtidge]

Great suggestion, tzamora! I'll give it a try.

-john

 

[tzamora]

This should work...

Select * from SW.ADDR_VERIFICATION
where QUADRANT = pQUAD
and STREET = pSTREET
and pADDRESS_NUMBER between LOW_ADDRESS and HIGH_ADDRESS
and STREET_TYPE between (CASE WHEN pSTREET_TYPE IS NULL THEN ' ' ELSE pSTREET_TYPE END) and
(CASE WHEN pSTREET_TYPE IS NULL THEN 'ZZZ' ELSE pSTREET_TYPE END)
;

******************************************
*****working sample***********************
******************************************
select ss.IBMREQD
from sysibm.sysdummy1 ss
where ss.IBMREQD between (case when IBMREQD is null then 'A' else 'Y' end) and
(case when IBMREQD is null then 'Z' else 'Y' end)
;

http://programmer-by-the-hour.com