January 21, 2008 at 8:04 am
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
January 21, 2008 at 8:15 am
You could change your case statement like:
CASE
WHEN [myRow] = 1 THEN 'True'
WHEN [myRow] IS NULL THEN 'False'
ELSE 'FALSE'
END
Andras
January 21, 2008 at 9:04 am
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?
January 22, 2008 at 3:18 pm
...unless you have SET ANSI_NULLS OFF in which case (NULL = NULL) tests true. But bad bad practice, so ignore this. 🙂
June 13, 2008 at 1:36 pm
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
June 13, 2008 at 1:47 pm
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?
June 13, 2008 at 1:50 pm
beautiful ... thank you.
June 17, 2014 at 11:07 am
Nice!!!
June 18, 2014 at 3:01 pm
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;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply