case statement for oracle ?

[Sebastian Kloska]

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

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

--
======================================================================
Dr. Sebastian Kloska; Max-Planck Institut für Mol. Pflanzenphysiologie
Karl Liebknecht Straße 25; 14476 Golm; Tel: (+49 030) 84131542
* NEW MAIL ADDRESS * mailto:klo...@mpimp-golm.mpg.de

[Thomas Kyte]

A copy of this was sent to Sebastian Kloska <klo...@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 tk...@us.oracle.com
Oracle Service Industries Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

[joop.b...@bigfoot.com]

Try

select decode(val,0,NULL,val) from tab;

In article <376E053B...@mpimp-golm.mpg.de>,

Sebastian Kloska <klo...@mpimp-golm.mpg.de> 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
>

> 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
>
> --

> ======================================================================
> Dr. Sebastian Kloska; Max-Planck Institut für Mol. Pflanzenphysiologie
> Karl Liebknecht Straße 25; 14476 Golm; Tel: (+49 030) 84131542
> * NEW MAIL ADDRESS * mailto:klo...@mpimp-golm.mpg.de
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.