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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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