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
 
DB2 Sorting of Alphanumeric field (usage ORDER BY )

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 Sorting of Alphanumeric field (usage ORDER BY )
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10309
Location: italy

PostPosted: Thu Mar 13, 2008 10:41 pm    Post subject: Reply to: DB2 Sorting of Alphanumeric field (usage ORDER BY
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    Post subject: Reply to: DB2 Sorting of Alphanumeric field (usage ORDER BY
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    Post subject:
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    Post subject: Reply to: DB2 Sorting of Alphanumeric field (usage ORDER BY
Reply with quote

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

Site Director


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

PostPosted: Sat Mar 15, 2008 4:11 am    Post subject:
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    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 Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am
No new posts JES2 job size field matching Windows ... SRICOBSAS All Other Mainframe Topics 4 Tue Sep 05, 2017 5:49 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 )SEL and )ENDSEL usage cvnlynn TSO/ISPF 4 Thu Jun 15, 2017 1:15 am
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm

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