Cursor with CASE expression



IBM's flagship relational database management system

Cursor with CASE expression

Postby saurabhgupta86 » Sun Jul 14, 2013 4:17 pm

Hi,

I am developing an application in which, i am retrieving two date fields say (DT_LAST and DT_LAST_FM) from a table and reading a date (CNTL-DATE) from control card.

DT_LAST and DT_LAST_FM are table fields.

CNTL-DATE is coming from control card.

The requirement is like that, i should use DT_LAST only in case DT_LAST > :CNTL-DATE
OR DT_LAST = DATE;

Incase, DT_LAST is equal to ZEROES, i should read, format (in some different format) and use DT_LAST_FM in my program and the selection criteria for DT_LAST_FM is same. i.e. DT_LAST_FM > :CNTL-DATE OR DT_LAST_FM = :CNTL-DATE

I have declared a cursor for the table, as shown below:

EXEC SQL                                                     
  DECLARE ABC CURSOR FOR                                 
   SELECT ID                                           
              ,TYPE                                           
     CASE DT_LAST                                   
      WHEN 0                                                 
          SELECT DT_LAST_FM                               
            FROM TBLABC                                     
           WHERE DATE(DT_LAST_FM) > DATE(:CNTL-DATE)   
                 OR DATE(DT_LAST_FM) = DATE(:CNTL-DATE)) 
      ELSE                                                   
          SELECT DT_LAST                             
           FROM  TBLCAS                                     
           WHERE DATE(DT_LAST) > DATE(:CNTL-DATE)
              OR DATE(DT_LAST) = DATE(:CNTL-DATE))
     END CASE                                               
    FROM TBLABC                             
    WHERE  POINT <> "9999999999"     
    ORDER BY POINT                         
    FOR FETCH ONLY WITH UR                   
END-EXEC.               

I am not sure whether the cursor declaration is correct or not.

If it is correct, then, when i will fetch the records, the date field (either DT_LAST_FM or DT_LAST) will be retrieved in the same variable, as i stated earlier the processing will differ based on the two values; how can i differentiate, whether the date retrieved is DT_LAST_FM or DT_LAST). Could you please suggest.

Regards,
Saurabh
saurabhgupta86
 
Posts: 13
Joined: Tue Aug 14, 2012 6:35 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Cursor with CASE expression

Postby dick scherrer » Mon Jul 15, 2013 1:02 am

Hello,

You need to use the Code tag to preserve alignment and improve readability . . .

I am not sure whether the cursor declaration is correct or not.
Suggest you try it and see.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post