CODEWITHSUNDEEP
sqlt-sql
Biswa
Biswa

Reputation: 331

case when in sql

I have table when contains who's two column contains data/null , i want to capture only rows which is not null.

example of t1

Application_channel | reason_for_joining
            call    | null
            null    | do not wish to provide
            null    | null

I want output as 

Status
 call
 do not wish to provide

query i wrote is somthing like:- 

select case 
when reason_for_joining is null and application_channel is not null then       application_channel
when application_channel is null and reason_for_joining is not null then 
reason_for_joining else application_channel end as status
from t1

the problem is its also taking null when both the column has null value which i dont want. Any help much appreciated.

Upvotes: 0

Views: 71

Answers (3)

Florian von Spiczak
Florian von Spiczak

Reputation: 393

He also wanted to filter those that are empty, so it would be:

select coalesce(Application_channel, reason_for_joining) as status
from t1
where coalesce(Application_channel, reason_for_joining) is not null

Alternatively you could also filter like this:

WHERE Application_channel IS NOT NULL OR reason_for_joining IS NOT NULL

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

Is case of two parameter ISNULL also do same work 

select ISNULL('call',null),isnull(null,'do not')

so in you query

select ISNULL(Application_channel, reason_for_joining) as status
from t1
where ISNULL(Application_channel, reason_for_joining) is not null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You want coalesce():

select coalesce(Application_channel, reason_for_joining) as status
from t1

Upvotes: 0

Related Questions