MVSFORUMS.com

[Pavani Donepudi]

Hi,

I am trying to compile a COBOL program with the following query

SELECT COL2,
COL3
INTO :WS-COL2
,:WS-COL3
FROM TABLE1
WHERE COL1 = 12345
ORDER BY
COL4 DESC
FETCH FIRST 1 ROW ONLY

Following is the error that I got.

DSNH109I E DSNHAPLY LINE 1838 COL 14 "ORDER BY" CLAUSE NOT PERMITTED

Is it that we cannot have ORDER BY and FETCH FIRST in embedded SQLs

[NASCAR9]

Pavani Donepudi, I believe you need to create a cursor with 'FETCH FIRST XX ROWS ONLY' This is from IBM man.
EXEC SQL DECLARE C1 CURSOR FOR
SELECT * FROM X.RMT_TAB
FETCH FIRST 50 ROWS Only;

Was this a new feature for V7.x? The way I read the manual it appears that way.
NASCAR9

[schintala]

I don't think you need to declare any cursor for the SQL. This SQL works well with ORDER BY CLAUSE. It must be some thing else in the program during compliation. Check the COBOL margins in the source code.

[nadh]

Hi

I think your COL4 in orderby should be mentioned in SELECT CLAUSE.

Cheers!
nadh

[schintala]

It is not mandatory to mention the column name in ORDER BY clause for select statement.

[NASCAR9]

Pavani Donepudi, I did a search(This Site) on 'fetch' and this one of the links I found:

http://www.mvsforums.com/helpboards/viewtopic.php?t=4202&highlight=fetch
NASCAR9

[suhas_pm]

following are the probable reasons for DSNH109I:
1. ORDER BY not allowed since result of SELECT is single row (at max)
2. SELECT used in cursor declaration can not have an INTO clause

Looks like mentioned query, if specified without 'FETCH FIRST 1 ROW ONLY' is likely to return more than one row, so most suitable solution to what is being tried to achieve may be the following: declare a cursor for this (and don't forget to remove the 'INTO' clause and also the FETCH FIRST... clause) and fetch the cursor once.

Else, write this way:
SELECT COL1,COL2
INTO <BLA>
FROM TABLE1
WHERE COL1 = 12345
AND COL4 = (SELECT MAX(COL4) FROM TABLE1 B WHERE B.COL1 = 12345)

Although this query will bomb if more than 1 rows have same max COL4 value, but feels like a better heck.

Hope this is at least somewhat useful.
-suhas

[Rama_Prayaga]

Hi Pavani Donepudi,

I have couple of questions.

1) does your SQL sytnax start with EXEC SQL statement and end with Semicolon (.

2) are you using DCLGEN variable are your own working storage variables.
Regards,
Rama

[Rama_Prayaga]

Pavani Donepudi,

One of the reason of for DSNH109I is