Re: Using IF statements in Select statements

casey_allred_at_my-deja.com wrote:
: I have the following SQL from SQLServer that I need to convert to PLSQL
: v 8:
 

: SELECT FD17 = CASE WHEN 17 - Sunset > 1 THEN 1 WHEN 17 - Sunset <
: 0 THEN 0 ELSE 17 - Sunset END
: FROM TABLE_FOO
 
: Sunset is a float
 

: I was thinking that this:
 

: SELECT DECODE(SIGN((17-Sunset)-1), 1, 1, -1, 0, 0, 17 - Sunset) as FD17
: FROM TABLE_FOO
 
: would do it but it does not account for the case when (17 - Sunset)<=1
: and > 0. Any ideas?

sign() and abs() are often useful to use with decode. Note that sign returns -1,0, or 1, so is more useful than you might realize.

I have found the idiom abs(sign(x-y))*z is often useful.

If I had more time, your question would be a nice one to ponder, but I can't, so I can't help more than that. Received on Wed Nov 01 2000 - 22:48:48 CST