CASE or DECODE with ORDER BY

[filmr]

Can't get this to work:

...
ORDER BY CASE v_OrderBy
WHEN 'Col1' THEN 1
WHEN 'Col2' THEN 2
ELSE 3
END DESC

Just seems to ignore the ORDER BY altogether.

 

[vijaychin]

ORDER BY decode(v_OrderBy,'Col1',1,'Col2',2,3) DESC

 

[oracleraj]

declare
v_var char(4) := 'Col1';
begin
declare
cursor c1 is select uomid,tomsrid,uomdesc from bduommst
order by to_number(decode(v_var,'Col1',1,'Col2',2,3),'0') desc;
begin
for r1 in c1
loop
dbms_output.put_line(r1.uomid);
end loop;
end;
end;


output
1
2
3
6
5
7
8
9
10
11
12
14
15
16
17
23
22
24
25
26
27
41
29
31
42
45

 

[oracleraj]

----- Sorry i tryied your solution but it doesn't work
---- Look at following code
----- Also see the output

declare
v_var char(4) := 'Col1';
begin
declare
cursor c1 is select uomid,tomsrid,uomdesc from bduommst
order by to_number(decode(v_var,'Col1',1,'Col2',2,3),'0') desc;
begin
for r1 in c1
loop
dbms_output.put_line(r1.uomid);
end loop;
end;
end;


output
1
2
3
6
5
7
8
9
10
11
12
14
15
16
17
23
22
24
25
26
27
41
29
31
42
45

 

[knechod]

ORDER BY CASE
WHEN v_OrderBy = 'Col1' THEN 1
WHEN v_OrderBy = 'Col2' THEN 2
ELSE 3
END DESC

DECODE is fine, but it's non-standard SQL.

Kevin