View previous topic :: View next topic
|
Author |
Message |
boo_sri
New User
Joined: 26 Jul 2005 Posts: 36 Location: charlotte
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
You are expecting wrong
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 |
|
|
boo_sri
New User
Joined: 26 Jul 2005 Posts: 36 Location: charlotte
|
|
|
|
Ok Let me know how to handle Mixed data types in ORDER BY clause. Any reference is highly appreciated. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
boo_sri
New User
Joined: 26 Jul 2005 Posts: 36 Location: charlotte
|
|
|
|
This really helps. Thanks... |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|