FW: conditional 'order by'

<snipped> to fit  

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Saturday, December 20, 2008 9:05 AM To: 'Randy.Steiner_at_nyct.com'
Cc: 'oracle-l_at_freelists.org'
Subject: RE: conditional 'order by'  

Doh! Is the column name code? I thought code was a parameter passed in. And based on the decode statement it is going to be 2 all the time unless it is the one character 'F' or the one character 'H'.

But the examples given are all four characters.  

Maybe if you hand in your working 2 step example I'll be able to understand your question.  

But for just a moment like me guess that &code is what is passed in and some column, let's call it sort_column, is a one character in length varchar2.  

then  

select sort_column from table

order by decode(sort_column,substr('&code',2,1),-256,0)+decode(sort_column, 'H',-256,0)+decode(sort_column,'F',-256,0)+ascii(sort_column)  

should do the trick.  

That is to say, modelling your sort formula, you're sorting by sort_column, but you're adjusting H and F lower than any value in the domain's range plus you're adjusting H or F lower than the other adjusted value.  

Since H and F are only two apart, I suppose the first -256 could be -3, but this makes it so you can use any ascii pair. Now if you have a bigger character set in mind you'll need to swap in a number of sufficient negative magnitude for the -256's and supply or find the relavant equivalent to ascii().  

I'm not at all sure I've answered your question, and depending on your meaning, maybe I've done it in four steps instead of two. (Four function calls, 3 decodes and an ascii).  

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