Order By Decode - Question

[aph]

Hi,

I have a query below

select * from emp
where deptno = &dpt
order by decode(.......)

If I enter dpt 10 then I need
order by ename

If I enter dpt 20 then I need
order by hiredate,job,sal

If I enter dpt 30 then I need
order by deptno,ename

How I can achive this. Thanks in advance.

[aph]

Hi,
Please correct me if I am wrong

select * from emp
where deptno = &dpt
order by decode(&dpt,10,ename,
20,hiredate||','||job||','||sal,
30,deptno||','||ename,'')


Thanks

[balajiyes]

A slight change in your query since you are anyway putting a filter on deptno:

select * from emp
where deptno = &dpt
order by decode(deptno,10,ename,
20,hiredate||','||job||','||sal,
30,deptno||','||ename,'')

Another observation is if you are already putting a filter on deptno, the sorting order for deptnos 10 and 30 pretty much amounts to the same, i.e., it would be on ename.