Results 1 to 5 of 5
  1. #1
    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
    Any Idea please?
    Last edited by afshar; 01-02-12 at 07:29.

  2. #2
    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. #3
    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. #4
    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. #5
    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
  •