Order By Clause in Singleton Select



IBM's flagship relational database management system

Order By Clause in Singleton Select

Postby thamodharan » Thu Jul 23, 2009 2:11 am

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...
thamodharan
 
Posts: 6
Joined: Wed Jun 17, 2009 3:09 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Order By Clause in Singleton Select

Postby dick scherrer » Thu Jul 23, 2009 3:44 am

Hello and welcome to the forum,

What happens if you run this query in spufi using literals instead of host variables?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Order By Clause in Singleton Select

Postby swd » Thu Jul 23, 2009 5:34 pm

Why not remove the ORDER BY - you don't need it!
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: Order By Clause in Singleton Select

Postby thamodharan » Thu Jul 23, 2009 10:59 pm

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...
thamodharan
 
Posts: 6
Joined: Wed Jun 17, 2009 3:09 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Order By Clause in Singleton Select

Postby dick scherrer » Fri Jul 24, 2009 12:08 am

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". . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Order By Clause in Singleton Select

Postby thamodharan » Fri Jul 24, 2009 1:38 am

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
thamodharan
 
Posts: 6
Joined: Wed Jun 17, 2009 3:09 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Order By Clause in Singleton Select

Postby dick scherrer » Fri Jul 24, 2009 1:46 am

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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Order By Clause in Singleton Select

Postby thamodharan » Fri Jul 24, 2009 2:04 am

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?
thamodharan
 
Posts: 6
Joined: Wed Jun 17, 2009 3:09 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Order By Clause in Singleton Select

Postby dick scherrer » Fri Jul 24, 2009 2:26 am

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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Order By Clause in Singleton Select

Postby swd » Fri Jul 24, 2009 2:05 pm

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.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post