Using a CASE function

Question

I am using Oracle and am trying to build out some sql for the following scenario:

On EMPLOYEES table, if employee has ADDRESS3 not equal to ' ', populate this field with ADDRESS2 else, populate this field with ADDRESS1.

...

, ADDRESS_LINE2 = NVL(( 
    SELECT (CASE t2.ADDRESS3 != ' ' THEN t2.ADDRESS2 ELSE t2.ADDRESS1 END)
    FROM EMPLOYEES t2
    WHERE t2.EMPLID = PS_Z_EXS_EMP_TBL_T.EMPLID
), t2.ADDRESS1)

...

but it keeps giving me an error message about missing the right parenthesis. When I comment this bit out though it runs fine. DOes anyone know what I'm doing wrong?

This appears to be missing the WHEN statement. CASE WHEN t2.ADDRESS3 != ' ' THEN t2.ADDRESS2 ELSE t2.ADDRESS1 END or CASE t2.ADDRESS3 WHEN != ' ' THEN t2.ADDRESS2 ELSE t2.ADDRESS1 END — xQbert
– xQbert, Commented Aug 13, 2014 at 19:30

Kim Berg Hansen · Accepted Answer · 2014-08-13 20:09:57Z

1

CASE has two variants - both needs WHEN clauses.

One variant can have complete and complex boolean expression in each WHEN clause:

CASE
   WHEN t2.ADDRESS3 != ' ' THEN t2.ADDRESS2
   ELSE t2.ADDRESS1
END

In the other variant each WHEN clause contain values to be tested for the CASE expression:

CASE t2.ADDRESS3
   WHEN ' ' THEN t2.ADDRESS1
   ELSE t2.ADDRESS2
END

The last one cannot do != so therefore "reversed" logic ;-)

answered Aug 13, 2014 at 20:09

Kim Berg Hansen

2,03913 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joel Coehoorn · Accepted Answer · 2014-08-13 19:20:58Z

0

You need the END keyword at the end of a case expression:

CASE t2.ADDRESS3 != ' ' THEN t2.ADDRESS2 ELSE t2.ADDRESS1 END

answered Aug 13, 2014 at 19:20

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

2 Comments

Joel Coehoorn Over a year ago

Then check for a mis-placed closing parentheses.

Andriy M Over a year ago

WHEN was also missing.

Collectives™ on Stack Overflow

Using a CASE function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related