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
 

 

SUBSTR and Order by clause - DB2

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

New User


Joined: 31 May 2007
Posts: 4
Location: Chennai

PostPosted: Fri Apr 22, 2011 12:44 am    Post subject: SUBSTR and Order by clause - DB2
Reply with quote

1. I want to select 5 fields from a db2 table of which 2 are varchar field of length 50 bytes.
2. Requirement is to view only the first 15 char of the 2 varchar field and also to sort those 2 varchar fields in ascending order.
3. I cannot use SUBSTR(Column_Name,1,15) in SELECT stament because ORDER BY clause is having the Column_Name.

I am looking for some guidance...


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

saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Fri Apr 22, 2011 2:28 am    Post subject:
Reply with quote

Hi Santosh,


Please find the query.

Code:
select field1,substr(field2,1,15),substr(field3,1,15) from table1
order by 1 asc, 2 asc, 3 asc with ur;


Regards
Sai
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Apr 22, 2011 3:00 am    Post subject:
Reply with quote

Quote:
with ur


should only be used when design permits.
otherwise, you can have incorrect results if a rollback occurs after your select.
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 Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts SQL Order By related question Joseph K Thomas DB2 8 Fri Mar 18, 2016 12:53 pm
No new posts DFSORT control statements in any order ? ronald masters DFSORT/ICETOOL 2 Tue Dec 15, 2015 11:22 pm
No new posts [SQL0029] INTO clause missing from em... HABBIE DB2 2 Fri Sep 04, 2015 3:54 pm


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