CODEWITHSUNDEEP
sqloracle-databaseselectcasecase-when
Csanchez
Csanchez

Reputation: 373

Oracle select many columns when are null or empry

I'm trying to get an user from a table when one or many columns are null

I mean, something like this

SELECT ID_USER,
    CASE
        WHEN NAME IS NULL
          THEN 'HAS NO NAME'
        WHEN SECON_NAME IS NULL
          THEN 'HAS NO SECOND'
        WHEN USER_NUM IS NULL
          THEN 'HAS NO NUM'
    END AS STATUS
FROM USER

is this possible? or there is an another way to get it?

So far I can only get second_name column

enter image description here

Upvotes: 0

Views: 40

Answers (2)

Gabriel Durac
Gabriel Durac

Reputation: 2760

CASE expressions exit after the first expression that evaluates as TRUE. So even if you have multiple conditions, you will only get one of them.

In order to get the users that have either a NULL name, NULL second name or a NULL num you can run this query:

SELECT ID_USER 
FROM USER
WHERE 
NAME IS NULL 
OR SECON_NAME IS NULL
OR USER_NUM IS NULL

If you want to see why they were returned you can use 

    SELECT ID_USER,
 CASE WHEN NAME IS NULL THEN 'HAS NO NAME ' else '' end
|| CASE WHEN SECON_NAME IS NULL THEN 'HAS NO SECOND ' else '' end
|| CASE WHEN USER_NUM IS NULL THEN 'HAS NO NUM' else '' end 
as STATUS
    FROM USER
    WHERE 
    NAME IS NULL 
    OR SECON_NAME IS NULL
    OR USER_NUM IS NULL

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142798

Something like this?

SQL> with users (id, name, second_name, user_num) as
  2    (select 1, 'Little', 'Foot', '100' from dual union all -- has all
  3     select 2, 'Scott' , null  , '200' from dual union all -- no second
  4     select 3, null    , 'Mike', '300' from dual union all -- no first
  5     select 4, null    , null  , '400' from dual           -- no first, no second
  6    )
  7  select id,
  8    trim(both '-' from
  9    decode(name, null, 'No first') ||'-'||
 10    decode(second_name, null, 'No second') || '-'||
 11    decode(user_num, null, 'No num')) msg
 12  from users;

        ID MSG
---------- -------------------------
         1
         2 No second
         3 No first
         4 No first-No second

SQL>

Upvotes: 1

Related Questions