Order By Clause in Singleton Select

[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...

[dick scherrer]

Hello and welcome to the forum,

What happens if you run this query in spufi using literals instead of host variables?

[swd]

Why not remove the ORDER BY - you don't need it!

[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...

[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". . .

[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

[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?

[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?

[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?

[swd]

After reading this post properly (!) you can't have an order by because (accoring to Quick Reference)

Code: Select all

                                                                           
 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.