Page 1 of 1

Cursor with CASE expression

PostPosted: Sun Jul 14, 2013 4:17 pm
by saurabhgupta86
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

Re: Cursor with CASE expression

PostPosted: Mon Jul 15, 2013 1:02 am
by dick scherrer
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.