Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Santosh.

Asked: July 12, 2016 - 2:46 pm UTC

Last updated: July 12, 2016 - 3:01 pm UTC

Version: 11G

Viewed 1000+ times

You Asked

Hello Gurus,

Please correct me if am framing wrong CASE statement using multiple columns

(CASE WHEN (ENA_PRE1 IS NULL AND ENA_RT1 IS NULL)
THEN RT_UNE IS NULL
ELSE RT_UNE END)

Thank you in Advance.

and Chris said...

You can use multiple columns in the when clause. But you can't assign a value in the then clause as you've done:

THEN RT_UNE IS NULL


What are you trying to do? Return null if the other columns are null? If so, just use null:

set null <null>
create table t (
  x int ,
 y date,
 z int
);

insert into t values (1, null, null);

select case when y is null and z is null then x is null else x end c
from t;

Error at Command Line : 11 Column : 49
Error report -
SQL Error: ORA-00905: missing keyword
00905. 00000 -  "missing keyword"

select case when y is null and z is null then null else x end c
from t;

C       
<null> 

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you it works like charm

Santosh Lingampeta, July 12, 2016 - 3:09 pm UTC

Thank you Chris.