| View previous topic :: View next topic |
| Author |
Message |
adarsh444
Beginner
Joined: 19 Sep 2006
Posts: 13
Topics: 9
|
 Posted: Sun Mar 04, 2007 1:47 am Post subject: Query in ORDER By clause Using Fetch First |
 |
|
I've got the following SQL in a Cobol/DB2 V7 program that is trying to
retrieve 1 row only using the FETCH FIRST 1 ROW ONLY clause using a
singleton select instead of a cursor.
| Code: |
EXEC SQL SELECT F_ITM_NMBR, EFCTV_DT, CNCL_DT, F_SCHDL_AMNT,
VLDTY_INDCTR
INTO :DCLTFIDTL.F_ITM_NMBR,
:DCLTFIDTL.EFCTV_DT,
:DCLTFIDTL.CNCL_DT,
:DCLTFIDTL.F_SCHDL_AMNT,
:DCLTFIDTL.VLDTY_INDCTR
FROM TFIDTL
WHERE F_ITM_NMBR = :LAST_FEE AND
EFCTV_DT <= '2003-09-30' AND
VLDTY_INDCTR = 'Y'
ORDER BY 2 DESC
FETCH FIRST 1 ROW ONLY;
|
The Precompile step though gives me the following error
DSNH109I E DSNHAPLY LINE 320 COL 15 "ORDER BY" CLAUSE NOT PERMITTED
Which seems to indicate that :
Embedded SELECT statements cannot include ORDER BY, because the result
cannot be more than a single row. |
|
| Back to top |
|
 |
blitz2
Beginner
Joined: 23 Jan 2007
Posts: 84
Topics: 14
|
| Posted: Mon Mar 05, 2007 4:34 am Post subject: |
|
|
Use a cursor. Quick-reference says, 'If both the FETCH FIRST clause and ORDER BY clause are specified, the ordering is performed on the entire result table prior to returning the first n rows'.
________
ALASKA DISPENSARIES
Last edited by blitz2 on Thu Mar 10, 2011 5:28 pm; edited 1 time in total |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 05, 2007 6:13 am Post subject: |
|
|
adarsh444,
With a singleton Sql you canNOT have an order by clause.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
adarsh444
Beginner
Joined: 19 Sep 2006
Posts: 13
Topics: 9
|
| Posted: Wed Mar 07, 2007 2:31 am Post subject: |
|
|
I used the below query and it worked successfully with out any error
and now i am able to get the desired result.But wonder why it failed previously.Does it means that it can be used with correlated subquery.Pls confirm
| Code: |
EXEC SQL
SELECT F_ITM_NMBR
,EFCTV_DT
,CNCL_DT
,SCHDL_AMNT
INTO :DCLTFIDTL.F_ITM_NMBR
,:DCLTFIDTL.EFCTV_DT
,:DCLTFIDTL.CNCL_DT
,:DCLTFIDTL.F_SCHDL_AMNT
,:DCLTFIDTL.VLDTY_INDCTR
FROM TFIDTL
WHERE F_ITM_NMBR = :LAST_FEE
AND EFCTV_DT <= '2003-09-30'
AND VLDTY_INDCTR = 'Y'
AND ITEM = (SELECT D_ITM_NMBR
FROM TF2DTL
WHERE VLDTY_INDCTR = 'Y')
ORDER BY 2 DESC
FETCH FIRST 1 ROW ONLY;
|
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
