ISNULL in a CASE statement???
-
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
-
You could change your case statement like:
CASEWHEN [myRow] = 1 THEN 'True'WHEN [myRow] IS NULL THEN 'False'ELSE 'FALSE'ENDAndras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
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? -
...unless you have SET ANSI_NULLS OFF in which case (NULL = NULL) tests true. But bad bad practice, so ignore this.
-
Andras Belokosztolszki (1/21/2008)
You could change your case statement like:CASEWHEN [myRow] = 1 THEN 'True'WHEN [myRow] IS NULL THEN 'False'ELSE 'FALSE'ENDAndras
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 distinctpo.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_datewhen pl.desired_recv_date IS NULLthen 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_faxfrompurchase_order as po,purc_order_line as plwherepo.status = 'R' andpo.id = pl.purc_order_id and(pl.desired_recv_date <= getdate()or (pl.desired_recv_date is null and po.desired_recv_date<=getdate())) andpl.order_qty - pl.total_received_qty > 0 --and--contact_fax is not nullorder bypo.vendor_id,po.id -
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 herewhen pl.desired_recv_date IS NULLthen 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? -
beautiful ... thank you.
-
Nice!!!
-
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