Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
syed-1919

New User


Joined: 26 Oct 2006
Posts: 12
Location: Chennai

PostPosted: Thu Nov 30, 2006 4:56 pm    Post subject: Use of clauses such as FETCH/ORDER BY within a sub-query
Reply with quote

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
Back to top
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Thu Nov 30, 2006 8:57 pm    Post subject: Re: Use of clauses such as FETCH/ORDER BY within a sub-query
Reply with quote

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

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/4.2?DT=20040216135741
Back to top
View user's profile Send private message
syed-1919

New User


Joined: 26 Oct 2006
Posts: 12
Location: Chennai

PostPosted: Fri Dec 01, 2006 9:51 am    Post subject: Re: Use of clauses such as FETCH/ORDER BY within a sub-query
Reply with quote

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
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Fri Dec 01, 2006 10:24 pm    Post subject: Re: Use of clauses such as FETCH/ORDER BY within a sub-query
Reply with quote

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

here talk is about usage of ORDER BY

http://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.

http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V91CK.PDF
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us