Sorting string in order by and case when

afshar · #1 (**permalink**) 01-02-12, 06:25

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
Any Idea please?

tonkuma · #2 (**permalink**) 01-02-12, 07:05

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
;

tonkuma · #3 (**permalink**) 01-02-12, 07:27

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
;

tonkuma · #4 (**permalink**) 01-02-12, 13:02

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
;

tonkuma · #5 (**permalink**) 01-04-12, 22:16

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech