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

DB2 Sorting of Alphanumeric field (usage ORDER BY )


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
boo_sri

New User


Joined: 26 Jul 2005
Posts: 36
Location: charlotte

PostPosted: Thu Mar 13, 2008 10:24 pm
Reply with quote

Hi -
I have a column with (PL_ID_C CHAR(9))

Values of the column are listed below:
1001
1002
40
20
25
AA9999
AB8965

So when i did SORT on this column (ORDER by PL_ID_C) i am getting the following order:

AB8965
AA9999
1001
1002
20
25
40

But i am expecting the output in order given below:

20
25
40
1001
1002
AB8965
AA9999

Please let me know how can i achive this one?
Any functions available like TRANSLATE, REPLACE?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Mar 13, 2008 10:41 pm
Reply with quote

You are expecting wrong icon_biggrin.gif
review the application specifications,
for the samples shown that' s what You get for alphanumeric fields

what You are asking for is a mixed sort where numeric only should be right aligned
to make them behave as proper numbers
and alphanumerics should be left alone,

so I stand by the suggestion in the second line

the simplest thing would be to edit when inserting the data into the db2 table
but then a few more problems would come out ....
Back to top
View user's profile Send private message
boo_sri

New User


Joined: 26 Jul 2005
Posts: 36
Location: charlotte

PostPosted: Fri Mar 14, 2008 8:22 pm
Reply with quote

Ok Let me know how to handle Mixed data types in ORDER BY clause. Any reference is highly appreciated.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Fri Mar 14, 2008 9:02 pm
Reply with quote

Try this I haven't tested it but it should work. Note there are 9 spaces in the literal before concat.

Code:

SELECT PL_ID_C FROM                                               
(SELECT PL_ID_C,                                                 
          RIGHT(('         ' CONCAT STRIP(PL_ID_D)),9) AS SORTKEY
 FROM TABLE1) A                                                   
ORDER BY A.SORTKEY;                                                 
Back to top
View user's profile Send private message
boo_sri

New User


Joined: 26 Jul 2005
Posts: 36
Location: charlotte

PostPosted: Sat Mar 15, 2008 1:40 am
Reply with quote

This really helps. Thanks...
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sat Mar 15, 2008 4:11 am
Reply with quote

Hello,

While you can "sort" the rows "out of sequence", how will this "sequence" be used in the remainder of the processing? Greater/less comparisons will not work.

Are ' 032' and ' 32' equal?

I'd encourage what Enrico suggests:
Quote:
review the application specifications,
You will (IMHO) be ahead if you use the standard ebcdic collating sequence.
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 Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Rotate partition-logical & physic... DB2 0
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts How to move the first field of each r... DFSORT/ICETOOL 5
Search our Forums:

Back to Top