Use of clauses such as FETCH/ORDER BY within a sub-query

syed-1919 · New User Joined: 26 Oct 2006 Posts: 12 Location: Chennai

Hi all,

Whenever I use Clauses such as FETCH/ORDER BY within a Sub-Query or In-line View, I get the message something similar to this:

-199. The use of Reserve word 'ORDER' following "" is not valid.

But when I execute the sub-query alone, it works perfectly fine.

For Ex. when I need the last 10 maximum values of a column to be joined with another table, I would ORDER the records in Descending and would fetch the first 10 rows within the sub-query to get my desired set of records. But this won't work.

I know there are methods available for my requirement. But I am curious to know why this method doesn't work though the sub-query independantly works.

Thanks 'n' Regards,
Syed

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

One can have multiple ORDER BY statements in a query, but only one per sub-select.

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/4.2?DT=20040216135741

syed-1919 · New User Joined: 26 Oct 2006 Posts: 12 Location: Chennai

Hi MFRASHEED,

Thanks for the reply. When I visited the link which u had specified in the previous post, I found that sub-select can only have the following clauses,

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause

I was curious to know why ORDER BY / FETCH clauses cannot be used in the Sub-select.

Hope my concerned is highlighted.

Thanks & Regards,
Syed

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

I pasted wrong link, check this out ( you can search in same manual also)

here talk is about usage of ORDER BY

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/4.4.2?ACTION=MATCHES&REQUEST=ORDER&TYPE=FUZZY&SHELF=&DT=20040216135741&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT

also here is one more link to SQL cookbook, Go to page 179 and there are some good examples.

mysite.verizon.net/Graeme_Birchall/cookbook/DB2V91CK.PDF