equivalent to case statement 2

[vadimg]

hi.

what is the equivalent to SQL Server's case statement in Oracle?

query:

select
case
when isnull(VALUE) then 'Value is null'
else VALUE
end
from TABLE

thanks

 

[gtarrant]

use DECODE

 

[dbtoo2001]

case:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1029291

 

[SantaMufasa]

Vadim,

Actually, the syntax for Oracle CASE is similar to SQL Server's syntax:

col a heading Commission|Pct format a13
select	 last_name
	,case when to_char(commission_pct) is null then 'Value is null'
	 else to_char(commission_pct) end a
from s_emp
where rownum <= 15
/

                          Commission
LAST_NAME                 Pct
------------------------- -------------
Garcia                    Value is null
Ngao                      Value is null
Nagayama                  Value is null
Quick-To-See              Value is null
Ropeburn                  Value is null
Urguhart                  Value is null
Menchu                    Value is null
Biri                      Value is null
Catchpole                 Value is null
Havel                     Value is null
Magee                     10
Giljum                    12.5
Sedeghi                   10
Nguyen                    15
Dumas                     17.5
[code]

But for this specific case (i.e., checking for NULL) there is even tighter code:
[code]
select	 last_name
	,nvl(to_char(commission_pct),'Value is null') a
from s_emp
where rownum <= 15
/
                          Commission
LAST_NAME                 Pct
------------------------- -------------
Garcia                    Value is null
Ngao                      Value is null
Nagayama                  Value is null
Quick-To-See              Value is null
Ropeburn                  Value is null
Urguhart                  Value is null
Menchu                    Value is null
Biri                      Value is null
Catchpole                 Value is null
Havel                     Value is null
Magee                     10
Giljum                    12.5
Sedeghi                   10
Nguyen                    15
Dumas                     17.5
[code]

Let us know if this is helpful.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 00:02 (08Jul04) UTC (aka "GMT" and "Zulu"), 17:02 (07Jul04) Mountain Time)