|
View previous topic :: View next topic
|
| Author |
Message |
Raveendra Maddila
New User
Joined: 21 Sep 2007
Posts: 2
Location: India
|
|
|
|
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 |
|
 |
senthilnathanj
New User
Joined: 31 Jul 2007
Posts: 47
Location: chennai
|
|
|
|
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 |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
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 |
|
|
rajesh_mbt
New User
Joined: 27 Mar 2006
Posts: 97
Location: India
|
|
|
|
| The ORDER BY B.PROC_DT DESC should not be mentioned in subset of select query. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| And also the use of FETCH FIRST ROW ONLY is invalid in a subquery. |
|
| Back to top |
|
|
Raveendra Maddila
New User
Joined: 21 Sep 2007
Posts: 2
Location: India
|
|
|
|
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 |
|
|
rajesh_mbt
New User
Joined: 27 Mar 2006
Posts: 97
Location: India
|
|
|
|
| Thank you for letting us know... |
|
| Back to top |
|
|
|
|
