MVSFORUMS.com

[spalanis]

In DB2 UDB Stored Procedure, I have a Character field of length 10. This field has both numeric and character data. See example below.

CA2004
CA2300
65161
100803
When I sort this data, I have the output in the below order.
100803
65161
CA2004
CA2300

But I need it in the below order.
65161
100803
CA2004
CA2300

Thanks in advance for any of your assistance

[CZerfas]

Hello Spalanis,

whatever you want to do, you are not performing a character sort on your output column. Perhaps you should consider adding leading zeroes to the numeric values, therefore "aligning them to the right".

If you sort the rows
065161
100803
CA2004
CA2300
you should achive the desired result.

regards
Christian

[spalanis]

I thought of adding a leading space to the form. But How can I achieve it through DB2 UDB SQL. Please advice.

[kolusu]

spalanis,

Try using the following ORDER BY (untested)

[spalanis]

Kolusu,

As always, thanks a lot for your selfless assistance here.

Srini

[NASCAR9]

Ok, I created a test table with a 10 character col. with the values above.
I tried my query with kolusu (UNTESTED) order by and my results where as follows:

[Dibakar]

Append leading spaces instead of zeroes -

[NASCAR9]

Dibakar, Thanks for your reply. I did try it and the results are still not like spalanis asked for in his first post.
_________________
Thanks,
NASCAR9

[papadi]

To get the output from "high" to "low", try ORDER BY . . . DESC.
_________________
All the best,

di

[kolusu]

NASCAR9/Dibakar,

I think OP just wanted a regular sort but with trailing spaces the values with numerics would be sorted differently. I basically gave him a query which would do the padding.

Kolusu

[Dibakar]

I think spalanis is more interested in 65161 and 100803.

None of the original list he gave appears to be sorted.

[NASCAR9]

[papadi]