# Thread: Sorting string in order by and case when

1. Registered User
Join Date
Jan 2012
Posts
1

## Unanswered: Sorting string in order by and case when

Hi
suppose I have table A with 3 column a and b as int, c as varchar
I want to sort with this fields in db2
for 2 input PI_IN = 1 or 2 or 3 and PI_DIR = 1 (ascending) or -1 descending
for a & b I can easily write
select * from A
order by
case
when PI_IN = 1 Then (PI_DIR * a)
when PI_IN = 2 Then (PI_DIR * b)
but I can not write
when PI_IN = 3 Then(PI_DIR * c)

I used HEX(C) but I got error
I used INT(C) but I got overflow convertion
Last edited by afshar; 01-02-12 at 07:29.

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Please try Example 1, though it is rather complex.
I'll try to find simpler solutions.

If the result was not the expected order, please publish your test data with one of the following ways.

(1) CREATE TABLE statement and INSERT statement.
or
(2) WITH common-table-expression.

Example 1:
Code:
```SELECT *
FROM  a
ORDER BY
CASE pi_dir
WHEN 1 THEN
CASE pi_in
WHEN 1 THEN
CHAR(SIGN(a)) || DIGITS(CASE WHEN a < 0 THEN -2147483648 - a ELSE a END)
WHEN 2 THEN
CHAR(SIGN(b)) || DIGITS(CASE WHEN b < 0 THEN -2147483648 - b ELSE b END)
WHEN 3 THEN
c
END
ELSE ''
END  ASC
, CASE pi_dir
WHEN -1 THEN
CASE pi_in
WHEN 1 THEN
CHAR(SIGN(a)) || DIGITS(CASE WHEN a < 0 THEN -2147483648 - a ELSE a END)
WHEN 2 THEN
CHAR(SIGN(b)) || DIGITS(CASE WHEN b < 0 THEN -2147483648 - b ELSE b END)
WHEN 3 THEN
c
END
ELSE ''
END  DESC
;```

3. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Another example which may be simpler than Exampe 1.

Example 2:
Code:
```SELECT *
FROM  a
ORDER BY
CASE pi_in
WHEN 1 THEN
pi_dir * a
WHEN 2 THEN
pi_dir * b
END  ASC
, CASE pi_dir
WHEN 1 THEN
c
END  ASC
, c    DESC
;```
Last edited by tonkuma; 01-02-12 at 14:13. Reason: Replace(simplify) second sort key. Replace third sort key to simple "c DESC".

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
An example without using a CASE expression.

Example 3:
Code:
```SELECT *
FROM  a
ORDER BY
pi_dir * ( (1 - pi_in / 2) * a + MOD(pi_in - 1 , 2) * b )
, SUBSTR( c , NULLIF(1 , - pi_dir) )
, c DESC
;```
Last edited by tonkuma; 01-02-12 at 14:08. Reason: Replace second sort key. Exchange expression for a and expression for b in first sort key.

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Another example without using a CASE expression, including test data and the result.

Example 4:
Code:
```WITH
a(a , b , c) AS (
VALUES
( 1 ,           5 , 'abc'     )
,( 2 ,           0 , 'xyz'     )
,( 3 ,          -9 , 'klmnopq' )
,( 4 , -2147483648 , 'def'     )
,( 5 ,  2147483647 , 'g'       )
)
, parm(pi_in , pi_dir) AS (
VALUES ( 2 , -1 )
)
SELECT *
FROM  a
, parm
ORDER BY
(1 - pi_in / 2) * pi_dir * a
, (3 - pi_in)     * pi_dir * b
, SUBSTR( c , NULLIF(1 , pi_dir) ) DESC
, c
;
------------------------------------------------------------------------------

A           B                    C       PI_IN       PI_DIR
----------- -------------------- ------- ----------- -----------
5           2147483647 g                 2          -1
1                    5 abc               2          -1
2                    0 xyz               2          -1
3                   -9 klmnopq           2          -1
4          -2147483648 def               2          -1

5 record(s) selected.```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•