CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Harsha
Harsha

Reputation: 11

extract 'column name' on basis of a condition

I have a table named Employee with fields Id, address, phone_no, salary, date_of_joining and stream. It contains just one row and the fields:

I want the output as the fields that contain NULL values, i.e. address, phone_no and date_of_joining. This needs to be executed in Oracle SQL. I am not getting any idea how to go about it.

Input:

EMPLOYEE                    
Id    Address   phone_no    salary      date_of_joining   stream
101   NULL      NULL        8,00,000    NULL              Big Data

Output:

Address,
phone_no,
date_of_joining

Upvotes: 1

Views: 143

Answers (2)

NikNik
NikNik

Reputation: 2301

You can do this in this way (same as Gordon's solution but in one record):

Select Id,
case when phone_no is null then 'phone_no, ' else '' end || 
case when Address is null then 'Address, ' else '' end ||
case when date_of_joining is null then 'date_of_joining' else '' end as Null_values
from t;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do this explicitly:

select 'Id' from t where Id is null union all
select 'Address' from t where Address is null union all
select 'phone_no' from t where phone_no is null union all
select 'salary' from t where salary is null union all
select 'date_of_joining' from t where date_of_joining is null union all
select 'stream' from t where stream is null;

Upvotes: 1

Related Questions