SQL switch from decode to case

Question

I have a query that does something like this...

SELECT * FROM ...
...
ORDER BY DECODE(APR(ACC.RATE,'X'), 'FIRST RATE', 1, 'SECOND RATE', 2, 3);

Because I am dealing with h2 database and h2 doesnt have decode and decode alias is very difficult to implement so I was wondering if I could convert this DECODE into CASE statement. How do I convert it?

Answer 1

Decode means:

CASE FirstParam
  WHEN SecondParam THEN ThirdParam 
  WHEN FourthParam THEN FifthParam
  WHEN SixthParam THEN Seventh... etcetera
  ELSE LastParam -- If number of params is odd, otherwise ELSE NULL is implied
END

So

CASE APR(ACC.RATE,'X')
  WHEN 'FIRST RATE' THEN 1
  WHEN 'SECOND RATE' THEN 2
  ELSE 3
END

Answer 2

ORDER BY DECODE(APR(ACC.RATE,'X'), 'FIRST RATE', 1, 'SECOND RATE', 2, 3);

There is a problem with your ORDER BY clause. Whenever the DECODE uses the default value, then the ordering will not be guaranteed. Your query will have correct ordering only for the values FIRST RATE and SECOND RATE, for values other than these two the order will never be guaranteed.

It is something like this:

FIRST RATE - 1
SECOND RATE - 2
Anything else - 3

So, all the values which is now specified to be sorted as value 3, will be in random.

Better way is:

ORDER BY DECODE(APR(ACC.RATE,'X'), 'FIRST RATE', 1, 'SECOND RATE', 2, 3), APR(ACC.RATE,'X')

Now, all the rows are guaranteed to be always in a specific order.

To write the same using CASE expression:

ORDER BY
CASE (APR(ACC.RATE,'X')
WHEN 'FIRST RATE' THEN
  1
WHEN 'SECOND RATE' THEN
  2
ELSE
  3
END, APR(ACC.RATE,'X')

A working example:

SQL> WITH DATA(id) AS(
  2  SELECT 'X' FROM dual UNION ALL
  3  SELECT 'A' FROM dual UNION ALL
  4  SELECT 'Z' FROM dual UNION ALL
  5  SELECT 'M' FROM dual UNION ALL
  6  SELECT 'I' FROM dual UNION ALL
  7  SELECT 'N' FROM dual
  8  )
  9  SELECT * FROM DATA
 10  ORDER BY
 11  CASE id
 12  WHEN 'M' THEN
 13    1
 14  WHEN 'I' THEN
 15    2
 16  WHEN 'N' THEN
 17    3
 18  ELSE
 19    4
 20  END, id
 21  /

I
-
M
I
N
A
X
Z

6 rows selected.

SQL>