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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy