EvanGWatkins
Reputation: 1457
T-SQL Case Statement confusion
This is a legacy query that I am cleaning up and I am getting confused with this case statement so any help would be greatly appreciated.
SELECT CASE
WHEN bitdelivered = 1 THEN
'' + CONVERT(CHAR(10), deliv.dtmdeliverydate, 101)
+ ' '
+ 'þ' + '
' -- this is a carriage return, do not remove it
WHEN bitdelivered = 0 AND deliv.dtmdeliverydate < Getdate() THEN
'' + CONVERT(CHAR(10), deliv.dtmdeliverydate, 101)
+ ' '
+ '¨' + '
'
ELSE
'' + CONVERT(CHAR(10), deliv.dtmdeliverydate, 101)
+ ' '
+ '¨' + '
'
END
Like I said it is very dirty, however when I run the entire query, if I get a 0 for bitdelivered, the casestatement works as it should, however if I get a 1 returned, then I return a null instead of the else block. Any ideas?
EDIT I have edited the code as shown below for easier readability and I still get null...
select
CONVERT(CHAR(10), deliv.dtmdeliverydate, 101) +
' ' +
CASE
WHEN bitdelivered = 1 THEN 'þ'
ELSE '¨'
END + '
'
EDIT x 2 Here is the entire (very nasty I know) sql query, I did not write this I am just trying to clean it up...
SELECT dbo.tblrequirements.guidrequirementid,
tblcontracting.strdescription AS
strcontracting,
dbo.viewawardnumbers.strcontractnumber AS
strawardnumber,
Ltrim(Rtrim(dbo.viewawardnumbers.strtonumber)) AS strtonumber,
dbo.tblrequirements.strdescription,
dbo.tblezquerycontractvalue.curtotalvalue,
dbo.tblcodescontractvehicles.strdescription AS
strcontractvehicle,
tblcustomer.stracronym AS strcustomer,
dbo.tblcodesrequirementstatuses.strdescription AS strstatus,
Substring(dbo.tblrequirements.strnotes, 0, 512) AS strnotes,
Coalesce (dbo.tblrequirements.guidfromid,
'00000000-0000-0000-0000-000000000000'
) AS guidfromid,
Coalesce (dbo.viewteammembers.guidpersonid,
'00000000-0000-0000-0000-000000000000') AS guidpersonid
,
dbo.viewcurrentoptions.dtmstart,
dbo.viewcurrentoptions.dtmend,
Dateadd(d, -60, dbo.viewcurrentoptions.dtmend) AS
dtm1stnoticedue,
Dateadd(d, -30, dbo.viewcurrentoptions.dtmend) AS
dtm2ndnoticedue,
dbo.tblcontractdates.dtmcontractstart,
dbo.tblcontractdates.dtmcontractend,
Isnull(dbo.viewteammembers.strshortname, ' Not Assigned') AS strshortname
,
dbo.tblezqueryfunding.curtotalfunded,
CASE
WHEN ( dbo.tblcodesrequirementstatuses.strdescription =
'Pre-Solicitation'
OR dbo.tblcodesrequirementstatuses.strdescription =
'Solicitation'
OR dbo.tblcodesrequirementstatuses.strdescription =
'Source Selection'
) THEN 'P'
WHEN ( Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'IT'
AND Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'OPS'
) THEN
'S'
ELSE 'C'
END AS strproctype,
dbo.tblcodesprocurementtypes.strcode,
dbo.tblcodesprocurementtypes.strdescription AS strprocdesc,
deliveries.dtmdeliverydate,
deliveries.bitdelivered,
CASE
WHEN ( dbo.tblcodesrequirementstatuses.strdescription =
'Pre-Solicitation'
OR dbo.tblcodesrequirementstatuses.strdescription =
'Solicitation'
OR dbo.tblcodesrequirementstatuses.strdescription =
'Source Selection'
) THEN 'Initial Start Date'
WHEN ( Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'IT'
AND Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'OPS'
) THEN
'POP'
ELSE 'Delivery Date'
END AS
colpopdelivdt,
CASE
WHEN ( dbo.tblcodesrequirementstatuses.strdescription =
'Pre-Solicitation'
OR dbo.tblcodesrequirementstatuses.strdescription =
'Solicitation'
OR dbo.tblcodesrequirementstatuses.strdescription =
'Source Selection'
) THEN 'Req Award Date'
WHEN ( Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'IT'
AND Coalesce (dbo.tblcodesprocurementtypes.strcode, '') <> 'OPS'
) THEN
'Option Notice'
ELSE 'Delivered'
END AS
coloptnotdeliv,
tblpresolicitations.dtmcontacted,
tblpresolicitations.dtmrequiredby,
tblpresolicitations.dtmawardnotice,
strjobid,
bitprimary
FROM dbo.tblrequirements
INNER JOIN dbo.tblcodesrequirementstatuses
ON dbo.tblcodesrequirementstatuses.strcode =
dbo.tblrequirements.strstatusid
LEFT OUTER JOIN dbo.tblezquerycontractvalue
ON dbo.tblezquerycontractvalue.guidrequirementid =
dbo.tblrequirements.guidrequirementid
LEFT OUTER JOIN dbo.viewawardnumbers
ON dbo.viewawardnumbers.guidrequirementid =
dbo.tblrequirements.guidrequirementid
LEFT OUTER JOIN dbo.tblezqueryfunding
ON dbo.tblezqueryfunding.strawardnumber =
dbo.viewawardnumbers.strawardnumber
LEFT OUTER JOIN dbo.viewcurrentoptions
ON dbo.tblrequirements.guidrequirementid =
dbo.viewcurrentoptions.guidrequirementid
LEFT OUTER JOIN dbo.viewteammembers
ON dbo.viewteammembers.guidrequirementid =
dbo.tblrequirements.guidrequirementid
LEFT OUTER JOIN dbo.tblcontracts
ON dbo.tblcontracts.guidrequirementid =
dbo.tblrequirements.guidrequirementid
LEFT OUTER JOIN dbo.tblcontractdates
ON dbo.tblcontractdates.guidrequirementid =
dbo.tblrequirements.guidrequirementid
LEFT OUTER JOIN dbo.tblcodescontractvehicles
ON dbo.tblcodescontractvehicles.strcode = dbo.tblcontracts.strvehicleid
LEFT OUTER JOIN dbo.tblorganizations AS tblcontracting
ON dbo.tblrequirements.guidfromid = tblcontracting.guidorgid
LEFT OUTER JOIN dbo.tblorganizations AS tblcustomer
ON dbo.tblrequirements.guidtoid = tblcustomer.guidorgid
LEFT OUTER JOIN dbo.tblpresolicitations
ON dbo.tblpresolicitations.guidrequirementid =
dbo.tblrequirements.guidrequirementid
LEFT OUTER JOIN dbo.tblcodesprocurementtypes
ON dbo.tblcodesprocurementtypes.strcode =
dbo.tblpresolicitations.strprocurementtypeid
LEFT OUTER JOIN (SELECT guidrequirementid,
REPLACE(REPLACE(REPLACE (
(select
ISNULL(CONVERT(CHAR(10), deliv.dtmdeliverydate, 101), '(no date)') +
' ' +
CASE
WHEN bitdelivered = 1 THEN 'þ'
ELSE '¨'
END + '
'
/*(SELECT CASE
WHEN bitdelivered = 1
THEN
''
+
CONVERT(CHAR(10),
deliv.dtmdeliverydate,
101) +
' '
+ 'þ' + '
'
WHEN bitdelivered = 0
AND deliv.dtmdeliverydate < Getdate() THEN
'' +
CONVERT(CHAR(10), deliv.dtmdeliverydate, 101) +
' '
+ '¨' + '
'
ELSE
'' + CONVERT(CHAR(10), deliv.dtmdeliverydate, 101) +
' '
+ '¨' + '
'
END*/
FROM tblclins clins
INNER JOIN tblcommodities commod
ON commod.guidclinid = clins.guidclinid
INNER JOIN tbldeliveries deliv
ON deliv.guidcommodityid = commod.guidcommodityid
WHERE clins.guidrequirementid = req.guidrequirementid
AND deliv.bitdelivered = 0
ORDER BY guidrequirementid,
deliv.dtmdeliverydate
FOR XML PATH('')), '<', '<'), '>', '>'), '&', '&') AS dtmdeliverydate
,
Stuff ((SELECT '|' + CAST(bitdelivered AS CHAR(1))
FROM tblclins clins
INNER JOIN tblcommodities commod
ON commod.guidclinid = clins.guidclinid
INNER JOIN tbldeliveries deliv
ON deliv.guidcommodityid = commod.guidcommodityid
WHERE clins.guidrequirementid = req.guidrequirementid
ORDER BY guidrequirementid
FOR XML PATH('')), 1, 1, '') AS bitdelivered
FROM tblrequirements req
GROUP BY guidrequirementid) deliveries
ON deliveries.guidrequirementid = dbo.tblrequirements.guidrequirementid
Upvotes: 0
Views: 975
Answers (4)
Conrad Frix
Reputation: 52645
The clause of the CASE Statement is this
WHEN bitdelivered = 1
So it will never go into the ELSE Statement which you seem to think it should by this statement "however if I get a 1 returned, then I return a null instead of the else block"
If you're getting nulls from this case statement its because deliv.dtmdeliverydate is NULL and CONCAT_NULL_YIELDS_NULL is set to ON
You should fix this using COALESCE or ISNULL. You could change the setting CONCAT_NULL_YIELDS_NULL to OFF, but I don't recommend this as this setting surprises people.
However as we figured out during our discussion the WHERE Clause on the derived table
WHERE clins.guidrequirementid = req.guidrequirementid AND deliv.bitdelivered = 0 means that
CASE WHEN bitdelivered = 1
will never be evaluated.
Upvotes: 2
StevieG
Reputation: 8709
if deliv.dtmdeliverydate is null when your bitdelivered is 1, then the resulting value will be null..
Upvotes: 0
gbn
Reputation: 432210
You can simplify it for one. I may have missed a few carriage returns
ISNULL(CONVERT(CHAR(10), deliv.dtmdeliverydate, 101), '(no date)') +
' ' +
CASE
WHEN bitdelivered = 1 THEN 'þ'
ELSE '¨'
END + '
'
Upvotes: 1
D'Arcy Rittich
Reputation: 171391
It is likely that deliv.dtmdeliverydate is NULL in that case.
If it is, then CONVERT(CHAR(10), deliv.dtmdeliverydate, 101) will yield NULL and concatenating that to a string will yield NULL.
Upvotes: 1