IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SUBSTR and Order by clause - DB2


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Rotate partition-logical & physic... DB2 0
No new posts To search DB2 table based on Conditio... DB2 1
No new posts DB2 Load - Sort Or order BY DB2 1
No new posts GDG all in sequence order JCL & VSAM 9
No new posts Combining more 4 files with sorted or... DFSORT/ICETOOL 3
Search our Forums:

Back to Top