Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Case statement in ORDER BY Clause

    Hi,

    I need to do case statement in ORDER BY Clause. It works if ORDER BY is only for one column. How do I do for multiple column.

    It works if

    Select EmpNo, EmpName, Salary
    from employee
    where EmpNo > 1000
    order by
    case when :sort-by-name = 'y'
    then EmpName
    else Salary
    end


    It doesn't work

    Select EmpNo, EmpName, Salary
    from employee
    where EmpNo > 1000
    order by
    case when :sort-by-name = 'y'
    then EmpName, EmpNo
    else Salary, EmpNo
    end

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT EmpNo
         , EmpName
         , Salary
      FROM employee
     WHERE EmpNo > 1000
    ORDER 
        BY CASE WHEN :sort-by-name = 'y'
                THEN EmpName
                ELSE Salary
            END
         , EmpNo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It works if

    Select EmpNo, EmpName, Salary
    from employee
    where EmpNo > 1000
    order by
    case when :sort-by-name = 'y'
    then EmpName
    else Salary
    end
    Apart from your issue, was it really worked?
    Because, usually EmpName must be CHAR or VARCHAR and Salary must be number.

    See Numeric in Rules for result data types - IBM DB2 9.7 for Linux, UNIX, and Windows
    Code:
    If one operand is… And the other operand is… The data type of the result is… 
    ...
    ...
    INTEGER            String                     DECFLOAT(34) 
    ...
    DECIMAL(w,x)       String                     DECFLOAT(34)
    So, result datatype must be DECFLOAT and most EmpName may not be able to convert to DECFLOAT.
    I guessed that you may want to replace "else Salary" to "else CHAR(Salary)".

    Anyhow, please try...
    Code:
    SELECT EmpNo, EmpName, Salary
     FROM  employee
     WHERE EmpNo > 1000
     ORDER BY
           CASE
           WHEN :sort-by-name = 'y' THEN
                EmpName
           ELSE CHAR(Salary)
           END
         , EmpNo
    ;

  4. #4
    Join Date
    Oct 2011
    Posts
    2
    Thanks a lot tonkuma!! It worked.

    The Empno and salary were just and example I gave. In actual scenario, the ORDER BY clause contains only CHAR. So it worked.

    But, DESC won't work. Is there a way to sort it on DESC?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Specify DESC after END of case expression and/or after ", EmpNo".

Posting Permissions

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