[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1287
  • Last Modified:

Order By using a parameter.........

How can I use a parameter to pick a sort condition.  I'm trying the following and it's not working.

Thanks in advance.

order by case when :param_sort = '1' then 'r.full_name'
when :param_sort = '2' then 'p.name'
when :param_sort = '3' then 't.prname'
else 'r.unique_name'
end
0
Weller0123
Asked:
Weller0123
2 Solutions
 
joebednarzCommented:
You just need to provide the parameter names, without the "bind" indicator... and don't include the quotes around the column name:

ORDER BY (CASE WHEN param_sort='1' THEN r.full_name
                               WHEN param_sort='2' THEN p.name
                               WHEN param_sort='3' THEN t.prname
                               ELSE  r.unique_name
                    END);
0
 
paquicubaCommented:
Use a simple decode, see the example:

select a,b,c,d from
 (select 5 a,2 b,3 c,4 d from dual
union all
select 4 a,1 b,2 c,3 d from dual
union all
select 3 a,0 b,1 c,2 d from dual)
order by decode(&param_sort,1,a,2,b,3,c,d) desc
 /
Enter value for param_sort: 1
old   7: order by decode(&param_sort,1,a,2,b,3,c,d) desc
new   7: order by decode(1,1,a,2,b,3,c,d) desc

         A          B          C          D
---------- ---------- ---------- ----------
         5          2          3          4
         4          1          2          3
         3          0          1          2
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now