CODEWITHSUNDEEP
sqloracle-database
user3586248
user3586248

Reputation: 163

Oracle SQL CASE statement checking multiple conditions

I am using Oracle SQL and am trying to fulfill the following logic: On PS_EMPLOYEES, if ADDRESS3 is not null and not equal to ' ', populate this field with ADDRESS2 else, populate this field with ADDRESS1.

I am able to get the following part working, but how do I make sure it isn't null too?

, ADDRESS_LINE1 = 
    SELECT (CASE WHEN t2.ADDRESS3 != ' ' THEN substr(t2.ADDRESS2, 1, 30) ELSE substr(t2.ADDRESS1, 1, 30) END)
    FROM PS_EMPLOYEES t2
    WHERE t2.EMPLID = PS_Z_EXS_EMP_TBL_T.EMPLID

Upvotes: 0

Views: 5945

Answers (2)

Twelfth
Twelfth

Reputation: 7180

I think you should be able to get by with an or statement in your case clause

SELECT (CASE WHEN t2.ADDRESS3 != ' ' or t2.ADDRESS3 is null THEN substr(t2.ADDRESS2, 1, 30) ELSE substr(t2.ADDRESS1, 1, 30) END)

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

You can combine whatever conditions you like in a CASE statement using AND and OR

(CASE WHEN t2.address3 IS NOT NULL AND
           t2.address3 != ' '
      THEN substr( t2.address2, 1, 30 )
      ELSE substr( t2.address1, 1, 30 )
  END)

Upvotes: 1

Related Questions