CODEWITHSUNDEEP
sqloracle-database
user3586248
user3586248

Reputation: 163

Using a CASE function

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?

Upvotes: 0

Views: 77

Answers (2)

Kim Berg Hansen
Kim Berg Hansen

Reputation: 2019

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

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416169

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

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

Upvotes: 0

Related Questions