IBM Mainframe Forum

by **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...

by **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?

by **swd** » Thu Jul 23, 2009 5:34 pm

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

by **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...

by **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". . .

by **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

by **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?

by **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?

by **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?

by **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)

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.

IBM Mainframe Forum

Order By Clause in Singleton Select

Order By Clause in Singleton Select

Re: Order By Clause in Singleton Select

Re: Order By Clause in Singleton Select

Re: Order By Clause in Singleton Select

Re: Order By Clause in Singleton Select

Re: Order By Clause in Singleton Select

Re: Order By Clause in Singleton Select

Re: Order By Clause in Singleton Select

Re: Order By Clause in Singleton Select

Re: Order By Clause in Singleton Select