T-SQL病例陈述混淆
问题描述：
这是一个传统的查询，我正在清理，我对这个case语句感到困惑，所以任何帮助将不胜感激。T-SQL病例陈述混淆
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
就像我说的这是非常肮脏，但是当我运行整个查询，如果我得到一个0 bitdelivered的casestatement的作品，因为它应该，但如果我有1回，然后我返回null而不是else块。有任何想法吗？
编辑我已编辑的代码如下所示，可读性更强，我仍然得到空...
select
CONVERT(CHAR(10), deliv.dtmdeliverydate, 101) +
' ' +
CASE
WHEN bitdelivered = 1 THEN 'þ'
ELSE '¨'
END + '
'
编辑×2这里是整个（很讨厌的，我知道的）SQL查询，我没有写这个，我只是试图把它清理干净......
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
答
case语句的子句是这个
WHEN bitdelivered = 1
所以它永远不会进入else语句，你认为它应该被这句话“但是如果我得到一个1返回，然后我返回null而不是别的块”
的。如果你从这个case语句越来越空了，因为deliv.dtmdeliverydate是NULL和CONCAT_NULL_YIELDS_NULL设置为ON
您应该使用COALESCE或ISNULL来解决此问题。您可以将设置CONCAT_NULL_YIELDS_NULL更改为关闭，但我不建议这样做，因为此设置令人惊讶。
但是正如我们在派生表中our discussion想通了WHERE子句
WHERE clins.guidrequirementid = req.guidrequirementid AND deliv.bitdelivered = 0意味着
CASE WHEN bitdelivered = 1
将永远不会被评估。
答
很可能deliv.dtmdeliverydate是在这种情况下NULL。
如果是，则CONVERT(CHAR(10), deliv.dtmdeliverydate, 101)将产生NULL，并将其连接到字符串将产生NULL。
答
您可以将其简化为一个。我可能已经错过了几回车
ISNULL(CONVERT(CHAR(10), deliv.dtmdeliverydate, 101), '(no date)') +
' ' +
CASE
WHEN bitdelivered = 1 THEN 'þ'
ELSE '¨'
END + '
'
+0
我现在使用了你的简化，但是它仍然返回null而不是'没有日期' – EvanGWatkins 2012-01-04 16:40:16
+1
@EvanGWatkins：在这种情况下，问题不在你给我们的代码中。 – gbn 2012-01-04 16:49:39
答
如果deliv.dtmdeliverydate为空当你bitdelivered为1，然后将得到的值将是空..
上帝保佑你，这是丑陋=/ – Yuck 2012-01-04 16:29:38
难道'deliv.dtmdeliverydate'为空？我希望不会因为你的'bitdelivered'是1。 – 2012-01-04 16:30:34
@PhilKlein不是不是null – EvanGWatkins 2012-01-04 16:33:51