Re: case statement for oracle ?

A copy of this was sent to Sebastian Kloska <kloska_at_mpimp-golm.mpg.de> (if that email address didn't require changing) On Mon, 21 Jun 1999 11:26:19 +0200, you wrote:

> Hi,
>
> Is their anything like a case statement in the form
>
> CASE
> WHEN val=0 then null
> ELSE val
> END
>
> for Oracle. My SQL books claim that 'CASE' is ANSI SQL but
> Oracle doesn't seem to support is. The only way to somehow
> emulate it seems to be a union in the form
>
>

case is ANSI SQL-92 at some level higher then entry level. sql92 has many levels of compliance. currently no vendor (to my knowledge) is certified above entry level. we all have features of higher levels but no one complies with a level above entry level.

that aside -- look at decode.

select decode( val, 0, NULL, val ) from T

will accomplish what you want.

> SELECT val from tab where val<>0
> UNION
> SELECT null from tab where val=0
>
> This would become rather ugly in my case because tab is a rather
>complex
> subquery which I would have to state twice.
>
> Thank you for any help...
>
> Sebastian

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 21 1999 - 07:09:28 CDT