Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ISNULL in a CASE statement??? Expand / Collapse
Author
Message
Posted Monday, January 21, 2008 8:04 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 28, 2013 5:14 AM
Points: 64, Visits: 129
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
Post #445488
Posted Monday, January 21, 2008 8:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #445493
Posted Monday, January 21, 2008 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,115, Visits: 14,984
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?
Post #445511
Posted Tuesday, January 22, 2008 3:18 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:13 PM
Points: 318, Visits: 1,183
...unless you have SET ANSI_NULLS OFF in which case (NULL = NULL) tests true. But bad bad practice, so ignore this. :)
Post #446144
Posted Friday, June 13, 2008 1:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 11, 2014 11:18 AM
Points: 54, Visits: 204
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

Post #517016
Posted Friday, June 13, 2008 1:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,115, Visits: 14,984
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?
Post #517028
Posted Friday, June 13, 2008 1:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 11, 2014 11:18 AM
Points: 54, Visits: 204
beautiful ... thank you.

Post #517032
Posted Tuesday, June 17, 2014 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:07 AM
Points: 1, Visits: 7
Nice!!!
Post #1582486
Posted Wednesday, June 18, 2014 3:01 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:00 AM
Points: 238, Visits: 651
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;
Post #1583439
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse