Case statement in ORDER BY Clause

gpaulose27 · #1 (**permalink**) 10-28-11, 16:00

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

r937 · #2 (**permalink**) 10-28-11, 20:37

Code:

SELECT EmpNo
     , EmpName
     , Salary
  FROM employee
 WHERE EmpNo > 1000
ORDER 
    BY CASE WHEN :sort-by-name = 'y'
            THEN EmpName
            ELSE Salary
        END
     , EmpNo

tonkuma · #3 (**permalink**) 10-28-11, 20:50

Quote:

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

Quote:

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
;

gpaulose27 · #4 (**permalink**) 10-29-11, 18:42

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?

tonkuma · #5 (**permalink**) 10-29-11, 22:18

Specify DESC after END of case expression and/or after ", EmpNo".

Internet Services

Web Development

Digital Marketing

Consumer Tech