Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ISNULL in a CASE statement???


ISNULL in a CASE statement???

Author
Message
Joe Doherty-260822
Joe Doherty-260822
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 180
Hi,

Would someone please point out why the following is incorrect / not working, and how it should be achieved...

CASE [myRow]
WHEN 1 THEN 'True'
WHEN NULL THEN 'False' -- this line is my concern
ELSE 'FALSE'
END

I want to know how to detect for NULL in a CASE statement.

I know logically I can exclude the 'when null...' line as it will be captured by the ELSE statement.

Thanks,

Joe
Andras Belokosztolszki
Andras Belokosztolszki
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1549 Visits: 1585
You could change your case statement like:

CASE
WHEN [myRow] = 1 THEN 'True'
WHEN [myRow] IS NULL THEN 'False'
ELSE 'FALSE'
END



Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8332 Visits: 18268
Joe -

Just in case you were wondering: the difference between your syntax (the so-called "simple case" syntax) and Andras' is that the "simple" version of the syntax assumes that the operator is =, so in your case - NULL=NULL, which by definition of NULL returns a result of UNKNOWN. So the case would fail.

The IS keyword isn't a comparision of a value, but a checking of a state instead.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Jacob Luebbers
Jacob Luebbers
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 1215
...unless you have SET ANSI_NULLS OFF in which case (NULL = NULL) tests true. But bad bad practice, so ignore this. Smile
TC-416047
TC-416047
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 213
Andras Belokosztolszki (1/21/2008)
You could change your case statement like:

CASE
WHEN [myRow] = 1 THEN 'True'
WHEN [myRow] IS NULL THEN 'False'
ELSE 'FALSE'
END



Andras


I'm using MSSQL 2000
I get a syntax error when using is null.
If I remove the is and leave the null there are no errors but not the desired result.
I would like to switch between the po date and the po line date if the po line date is null.
Here is the query:

select distinct
po.vendor_id,
po.id,
pl.line_no,
pl.part_id,
pl.order_qty - pl.total_received_qty as "Qty Due",
convert(char(12),po.desired_recv_date,101) as "Po Want Date",

case pl.desired_recv_date
when pl.desired_recv_date IS NULL
then convert(char(12),po.desired_recv_date,101)
else convert(char(12),pl.desired_recv_date,101)
end as "Line Want Date",

datediff(dd,getdate(), pl.desired_recv_date) as "Days Late",
po.contact_first_name,
po.contact_last_name,
po.contact_fax

from
purchase_order as po,
purc_order_line as pl


where
po.status = 'R' and
po.id = pl.purc_order_id and
(pl.desired_recv_date <= getdate()or (pl.desired_recv_date is null and po.desired_recv_date<=getdate())) and
pl.order_qty - pl.total_received_qty > 0 --and
--contact_fax is not null

order by
po.vendor_id,po.id


Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8332 Visits: 18268
Todd - when you use the "complex expression" version of CASE, don't list the column name ouside of the WHEN.

As in -


case --<<<<<<--LOOK - remove "pl.desired_recv_date" from here
when pl.desired_recv_date IS NULL
then convert(char(12),po.desired_recv_date,101)
else convert(char(12),pl.desired_recv_date,101)
end as "Line Want Date",




----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
TC-416047
TC-416047
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 213
beautiful ... thank you.
shashika7
shashika7
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Nice!!!
david.tyler
david.tyler
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 663
Another spin on it using isnull in the case:

create table #tmpTST
(
MyBit bit NULL
)

insert into #tmpTST
select 1
union all select 0
union all select NULL;

select
case isnull(MyBit,0)
when 1 then 'True'
when 0 then 'False'
end AS MyBit
from
#tmpTST;

drop table #tmpTST;
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search