Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.