Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

ORDER BY CLAUSE in CURSOR

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Raveendra Maddila

New User


Joined: 21 Sep 2007
Posts: 2
Location: India

PostPosted: Sun Mar 15, 2009 8:28 am    Post subject: ORDER BY CLAUSE in CURSOR
Reply with quote

Hi All,

Could anyone please advise what is wrong with following query -

Code:
EXEC SQL                                                   
          DECLARE ACCT_DTLS_CSR CURSOR FOR                       
           SELECT A.TR_NO,                               
                  A.ACCT_NO,                             
                  A.ASGN_TR_NO                               
           FROM RJCT_REBT A                                 
           WHERE A.PROC_DT =                                 
                (SELECT B.PROC_DT                             
                 FROM   RJCT_REBT B                         
                 WHERE  B.TR_NO     = A.TR_NO   AND
                        B.ACCT_NO   = A.ACCT_NO AND
                        B.PROC_DT BETWEEN :WS30-START-DATE AND :WS30-END-DATE
                 ORDER BY B.PROC_DT DESC                     
                 FETCH FIRST ROW ONLY)                           
END-EXEC     


I am receiving the following error message -

DSNH199I E DSNHPARS LINE 897 COL 24 INVALID KEYWORD "ORDER"; VALID SYMBOLS ARE: ) UNION EXCEPT

Appreciate your response.

Thanks,
Raveendra.
Back to top
View user's profile Send private message

senthilnathanj

New User


Joined: 31 Jul 2007
Posts: 47
Location: chennai

PostPosted: Sun Mar 15, 2009 2:24 pm    Post subject: Reply to: ORDER BY CLAUSE in CURSOR
Reply with quote

HI,

ORDER BY clause would not come in SUBQUERY.
I don't see a reason for doing that either ... Your subquery selects ALL "RJCT_REBT" (you'd like to have them randomly ordered), and then - from that set (all RJCT_REBT) you again select all RJCT_REBT.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Mon Mar 16, 2009 12:09 pm    Post subject: Reply to: ORDER BY CLAUSE in CURSOR
Reply with quote

Raveendra Maddila, someone would be able to help you with the query if you state the complete requirement.
Not sure if this you are looking for ...but you may try Selecting the MAX of PROC_DT with your Date checks.

WTF
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Mon Mar 16, 2009 12:49 pm    Post subject:
Reply with quote

The ORDER BY B.PROC_DT DESC should not be mentioned in subset of select query.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Mar 16, 2009 1:05 pm    Post subject:
Reply with quote

And also the use of FETCH FIRST ROW ONLY is invalid in a subquery.
Back to top
View user's profile Send private message
Raveendra Maddila

New User


Joined: 21 Sep 2007
Posts: 2
Location: India

PostPosted: Thu Mar 19, 2009 5:42 am    Post subject:
Reply with quote

Thank you Rajesh and Bharath.

ORDER BY and FETCH FIRST ROW should not have been placed in the SUB-QUERY.

The following query worked fine -

Code:
EXEC SQL                                                   
          DECLARE ACCT_DTLS_CSR CURSOR FOR                       
           SELECT A.TR_NO,                               
                  A.ACCT_NO,                             
                  A.ASGN_TR_NO                               
           FROM RJCT_REBT A                                 
           WHERE A.PROC_DT =                                 
                (SELECT MAX(B.PROC_DT)                             
                 FROM   RJCT_REBT B                         
                 WHERE  B.TR_NO     = A.TR_NO   AND
                        B.ACCT_NO   = A.ACCT_NO AND
                        B.PROC_DT BETWEEN :WS30-START-DATE AND :WS30-END-DATE)
END-EXEC     


Thank you for all your inputs.

Regards,
Raveendra
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Thu Mar 19, 2009 10:40 am    Post subject: Reply to: ORDER BY CLAUSE in CURSOR
Reply with quote

Thank you for letting us know...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us