0

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.

Biswa
  • 331
  • 6
  • 22
  • Hint : `COALESCE()` with `WHERE` clause. – Yogesh Sharma Oct 25 '18 at 12:21
  • @YogeshSharma what's the difference between `COALESCE()` and `ISNULL()`? – Gonzalo Lorieto Oct 25 '18 at 12:33
  • 1
    @GonzaloLorieto. . . For instance i would say `COALESCE()` is ANSI SQL Function which works almost in all `DBMS`, while `ISNULL()` is only `SQL Server` specific, apart from that it functionally behaves different. – Yogesh Sharma Oct 25 '18 at 12:37
  • And in `SQL Server`? – Gonzalo Lorieto Oct 25 '18 at 12:52
  • 1
    @GonzaloLorieto COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine. Both ISNULL and COALESCE can be used to get the same results but there are some differences. 1. Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence – Biswa Oct 25 '18 at 12:55

3 Answers3

3

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
Ilyes
  • 14,640
  • 4
  • 29
  • 55
Florian von Spiczak
  • 373
  • 1
  • 8
  • von spickzak, thanks buddy, i have used the atternative one, and it worked . Much appreciated your help. i have also tried coalesce. – Biswa Oct 25 '18 at 12:34
0

You want coalesce():

select coalesce(Application_channel, reason_for_joining) as status
from t1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thanks brother Gordon. i have used WHERE Application_channel IS NOT NULL OR reason_for_joining IS NOT NULL seems to fix my issue. any difference between coalesce and is not null? – Biswa Oct 25 '18 at 12:36
  • Basically ISNULL takes only two arguments while COALESCE takes multiple arguments (it returns the first non-NULL). For more details see here: https://stackoverflow.com/questions/7408893/using-isnull-vs-using-coalesce-for-checking-a-specific-condition – Florian von Spiczak Oct 25 '18 at 12:40
0

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
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63