Order by in EMBEDDED SQL?

Vani venkateshmurthy · New User Joined: 20 Nov 2004 Posts: 1

I am using an embedded SQL ( host language - COBOL) Query which has ORDER BY clause and FETCH WITH FIRST 1 ROW ONLY. When i run the query in QMF, it works fine. But it gives an error 'ORDER BY NOT PERMITTED' when the program is put for compilation. Can anyone explain why ORDER BY and FETCH WITH FIRST 1 ROW ONLY cannot be used together in Embedded SQL?

janardhana Reddy · New User Joined: 20 Oct 2004 Posts: 17 Location: Bangalore

You can use "optimize n rows". I forgot sybntax. You can refer book

ksivapradeep · New User Joined: 30 Jul 2004 Posts: 95

hi ,

i think u have to put where clause ie.

SELECT * FROM TABLE-NAME WHERE FETCH FIRST N ROWS ONLY
regards,
siva pradeep

ovreddy · Posted: Wed Dec 08, 2004 5:34 pm

Hi,

Both will work no problem. just use ORDER BY as a last clause that is after all other where predicates. Ok .

i.e.

select col1 from tab1 where fetch first 1 row only order by col1;

richa gupta · New User Joined: 29 Jul 2007 Posts: 8 Location: noida

Hello,

I am getting same error as mentioned above by Vani. I am even getting the ORDER BY error if I comment out FETCH FIRST ONE 1 ROW ONLY. These are working fine when run in QMF. Please help.

CICS Guy · Posted: Tue Oct 09, 2007 5:26 pm

I know it's a dumb question, but what is the select and what is the error.....

richa gupta · New User Joined: 29 Jul 2007 Posts: 8 Location: noida

I want to select rows in a cursor arranged on descending order of a field, which can be easily retrieved by ORDER BY clause.

I am getting this error:
"ORDER BY" CLAUSE NOT PERMITTED