RE: conditional 'order by'

Does

ORDER BY substr(code,2,1)  

work?  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Friday, December 19, 2008 1:47 PM
To: eugene.pipko_at_unionbay.com; oracle-l_at_freelists.org Subject: RE: conditional 'order by'  

You've got me really confused. Are F, 0, H, 1, and 2 the names of columns?  

If so, how many columns that are candidates to order by do you have?  

Are you intending to generate queries to execute or did I miss the memo on a new syntax involving order by?  

Regards,  

mwf  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eugene Pipko
Sent: Friday, December 19, 2008 11:57 AM To: 'oracle-l_at_freelists.org'
Subject: conditional 'order by'  

Hi all,

I have a query that needs to be ordered by based on the second letter of the code passed in it.

For instance if the code is '9F9Q' then I'd order by 'F' , then by 'H', then by any other.

If the code is '7H7Q' then I'd order by 'H', then by 'F', then by any other.

I know that I can do it in 2 steps, but is it possible to do in one sql statement?  

Select ...

From ...

Where ....

If substr(code,2,1)='F' then

                Order by decode(code,'F',0,'H',1,2);

Elsif substr(code,2,1)='H' then

                Order by decode(code,'H',0,'F',1,2);

End if;    

Thanks,    

Eugene

P Please consider the environment before printing this e-mail.  

--
http://www.freelists.org/webpage/oracle-l