asked on

SQl Server 2008 R2 - Case When Null still equals NULL

Hi Experts,

I have this part of a query in a select statement:

,(Select Distinct Case when Plug = NULL then 'No' Else 'Yes' End
From Table) as Top25

Open in new window

But I keep getting a NULL value in the output

Top25
Yes
Yes
Yes
NULL
Yes
Yes
NULL
Yes
Yes
Yes

Please help and thanks....

Kent Olsen

It's possible that the row contains the string "NULL" in that column.

Éric Moreau

you cannot use "= null", you need to use "is null"

Amour22015

ASKER

Thanks Experts,

I also tried: Case when (Field is Null) then 'No' Else 'Yes'

When record matches then yes else when no matching record then no

Please help and thanks...

Kent Olsen

Try again.

SELECT count(*) FROM table WHERE Plug = 'NULL';

SELECT count(*) FROM table WHERE Plug is NULL;

SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Looks like I might be doing this wrong.

If Plug does not exist in the table then 'No' else 'Yes'

So maybe I should be doing this in a Left outer Join? But then I would be getting ALL that don't match as 'NO' even the records that are not involved.

Maybe:

Table A
Left Outer Join (Select Case When Plug Is Null Then 'No' else 'Yes' End from Table B) YN
YN.Plug = A.Plug

Please help and thanks...

Éric Moreau

Can you give us the big picture (table definitions and maybe data sample) because currently it just doesn't make any sense trying to JOIN on yes/no!

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Hi Experts,

I ended up:
Select
Case when B.plug is Null 'No' else 'Yes' end as Top25

From Table A
Inner Join Table B
On B.Plug = A.Plug

If no record in Table B then No else Yes

It is working now...

Thanks for helping me...

Amour22015

ASKER

Thanks for helping