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
 

 

why a DB2 column used in ORDER BY is mandatory in SELECT too

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
mf_user
Currently Banned

New User


Joined: 05 Jan 2006
Posts: 47

PostPosted: Mon Nov 26, 2007 6:43 pm    Post subject: why a DB2 column used in ORDER BY is mandatory in SELECT too
Reply with quote

Hi,

I was just going through a program and found that a SELECT query with ORDER BY. This is an embedded query in a COBOL-DB2 program.

This particular query has a SELECT statment with four columns from DB2 table and the ORDER BY is having a different column name (not from the four column names of SELECT). Is it possible?

Can we have such a query? I read that if a column has to be used in ORDER BY then it must be mentioned in SELECT also.

Please explain.

Thanks.
Back to top
View user's profile Send private message

muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Mon Nov 26, 2007 7:17 pm    Post subject: Reply to: why a DB2 column used in ORDER BY is mandatory in
Reply with quote

Quote:
Can we have such a query? I read that if a column has to be used in ORDER BY then it must be mentioned in SELECT also.


Not So.It is enought that if the columns are present in the table which are given in the SQL


This is what the manual says for your question

Quote:
If the column name is not found in the result table, it must unambiguously identify a column of a table, view, or nested table expression in the FROM clause of the subselect.


Correct me if i am wrong.
Back to top
View user's profile Send private message
mf_user
Currently Banned

New User


Joined: 05 Jan 2006
Posts: 47

PostPosted: Mon Nov 26, 2007 9:03 pm    Post subject: Thx
Reply with quote

Sounds interesting. Any more thoughts on this?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Nov 26, 2007 9:21 pm    Post subject: Re: Thx
Reply with quote

mf_user wrote:
Sounds interesting. Any more thoughts on this?

I think it used to be required but not anymore, not sure when the change was made.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Dec 03, 2007 6:15 pm    Post subject:
Reply with quote

Hi,

Yeah is should be this way only...
Quote:
I think it used to be required but not anymore, not sure when the change was made.

When one writes a query then he is creating a 'view' of 'that' table for which the query is written..so even by intuition one would like to "order" that 'view' on the basis of some column in that 'view' ..not on some arbitrary column from the base table.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Dec 03, 2007 8:18 pm    Post subject:
Reply with quote

Quote:

Yeah is should be this way only...


so even though the column is not necessary, you want it there anyway? That is not efficient. The proper way would be to document the procedure so that the results are anticipated.
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 -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
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 SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am


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