Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6968
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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Sorting on text - but in non-alphabet... Roy Ware SYNCSORT 5 Wed Aug 23, 2017 9:15 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us