Page 1 of 2
Order By Clause in Singleton Select
Posted:
Thu Jul 23, 2009 2:11 am
by thamodharan
Hi All,
I need to know whether the Order By clause can be used in singleton select.
SELECT CUST_NMBR, LAST_UPDT_DT
INTO :WS-CUST-NMBR,:WS-LAST-UPDT
FROM CUST
WHERE END_DT >= :WS-PARM-DATE
AND EFF_DT <= :WS-PARM-DATE
ORDER BY LAST_UPDT_DT DESC
FETCH FIRST 1 ROW ONLY
the above was the query I wanted to execute in singleton mode. If I compile the program, I am getting ' ORDER BY NOT PERMITTED' error. Can any one suggest me what can be done for this requirement.
Thanks in advance...
Re: Order By Clause in Singleton Select
Posted:
Thu Jul 23, 2009 3:44 am
by dick scherrer
Hello and welcome to the forum,
What happens if you run this query in spufi using literals instead of host variables?
Re: Order By Clause in Singleton Select
Posted:
Thu Jul 23, 2009 5:34 pm
by swd
Why not remove the ORDER BY - you don't need it!
Re: Order By Clause in Singleton Select
Posted:
Thu Jul 23, 2009 10:59 pm
by thamodharan
Hi,
1. This query works fine in SPUFI. I tried this before coming into this forum itself.
2. I need Order By here, because i want the LAST_UPDT_DT to be latest.
then I tried using Sub-query. But its taking more CPU time when compared to the pgm which is using cursor for the same query...
Please help me out to avoid cursor for this query...
Re: Order By Clause in Singleton Select
Posted:
Fri Jul 24, 2009 12:08 am
by dick scherrer
Hello,
Why not remove the ORDER BY - you don't need it!
Why might you believe this?
This query works fine in SPUFI. I tried this before coming into this forum itself.
Keep in mind that spufi generates a cursor "under the covers". . .
Re: Order By Clause in Singleton Select
Posted:
Fri Jul 24, 2009 1:38 am
by thamodharan
Hi,
Why not remove the ORDER BY - you don't need it!
Why might you believe this?
I should use ORDER BY here, then only I can get the Latest ( updated ) customer.
This query works fine in SPUFI. I tried this before coming into this forum itself.
Keep in mind that spufi generates a cursor "under the covers". . .
I really don't understand this
Thanks
And a change in the query, ( I missed this last time. sorry...)
SELECT CUST_NMBR, LAST_UPDT_DT
INTO :WS-CUST-NMBR,:WS-LAST-UPDT
FROM CUST
WHERE CUST_LOC_NMBR = :WS-CUST-LOC-NMBR
AND END_DT >= :WS-PARM-DATE
AND EFF_DT <= :WS-PARM-DATE
ORDER BY LAST_UPDT_DT DESC
FETCH FIRST 1 ROW ONLY
Re: Order By Clause in Singleton Select
Posted:
Fri Jul 24, 2009 1:46 am
by dick scherrer
Hello,
I really don't understand this
SPUFI runs with a cursor internally. . .
You mentioned that you want to avoid using a cursor in your code. To be sure that your process always works correctly, i believe you need the cursor. The spufi query that
does work uses a cursor.
Where is
"I am getting ' ORDER BY NOT PERMITTED' error. " shown? What is the message id?
Re: Order By Clause in Singleton Select
Posted:
Fri Jul 24, 2009 2:04 am
by thamodharan
Hi,
Where is "I am getting ' ORDER BY NOT PERMITTED' error. " shown? What is the message id?
This I got while compiling the Application program.below is the error message,
DSNH109I E DSNHAPLY LINE 57 COL 38 "ORDER BY" CLAUSE NOT PERMITTED
I have the cursor for this already. So far, I tried to avoid the cursor for this. But you mean to say that I should use a cursor for this right?
Re: Order By Clause in Singleton Select
Posted:
Fri Jul 24, 2009 2:26 am
by dick scherrer
Hello,
In front of this "DSNH109I", is there an IGYxxxx message id?
But you mean to say that I should use a cursor for this right?
I believe so.
What version of db2 is used on your system? Which version of COBOL?
Re: Order By Clause in Singleton Select
Posted:
Fri Jul 24, 2009 2:05 pm
by swd
After reading this post properly (!) you can't have an order by because (accoring to Quick Reference)
DSNH109I E csectname LINE nnnn COL cc type CLAUSE NOT PERMITTED
Explanation: The type is INTO, ORDER BY, FOR UPDATE, , USING, LARGE, or
CORRELATION NAME.
o A SELECT INTO statement cannot include ORDER BY, because the result
cannot be more than a single row.
So, Dick is correct you will need a Cursor as you are doing a SELECT INTO. This way you can have the ORDER BY.
But.... have you tried SELECT CUST_NMBR, MAX(LAST_UPDT_DT) and remove the ORDER BY, this way you'll get the latest last update date without a cursor. See if that works.