时间戳

首页 > 解决方案 > 简化值+空检查

sql - 简化值+空检查

问题描述

我想简化我实际拥有的代码。我想简化时遇到问题，例如：

代替

(CF3.STRINGVALUE = 'Empty' OR CF3.STRINGVALUE IS NULL)

也许

(CF3.STRINGVALUE = 'Empty' OR IS NULL)

但它不允许我

jis.created, 
jis.resolutiondate,
IIF(CF3.STRINGVALUE like 'IDR-%','SI','NO') AS 'Massive',
cfo8.customvalue AS 'Solved in first go',
IIF(DATEDIFF(ss, LAG(jis.resolutiondate) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC), jis.CREATED) < 604800 AND (CF3.STRINGVALUE = 'Empty' OR CF3.STRINGVALUE IS NULL) AND (cfo8.customvalue = 'NO' OR cfo8.customvalue IS NULL) AND (ist.pname = 'Avería (FTTH)' OR ist.pname ='Avería (xDSL)') AND (LAG(ist.pname, 1, 0) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) = 'Avería (FTTH)' OR LAG(ist.pname, 1, 0) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) = 'Avería (xDSL)'), 'SI', 'NO') AS 'Reitero CM',
IIF(DATEDIFF(ss, LAG(jis.resolutiondate) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC), jis.CREATED) < 604800 AND cfo1.customvalue = LAG(cfo1.customvalue, 1, 0) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) AND (CF3.STRINGVALUE = 'Empty' OR CF3.STRINGVALUE IS NULL) AND (cfo8.customvalue = 'NO' OR cfo8.customvalue IS NULL) AND (ist.pname = 'Avería (FTTH)' OR ist.pname ='Avería (xDSL)') AND (LAG(ist.pname, 1, 0) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) = 'Avería (FTTH)' OR LAG(ist.pname, 1, 0) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) = 'Avería (xDSL)'), 'SI', 'NO') AS 'Reitero MM',
cfo1.customvalue AS 'Motivo de la Averia',
LAG(cfo1.customvalue, 1, 0) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) AS 'Motivo anterior de la Averia'

FROM [DWH].[JIR].[jiraissue] jis
LEFT JOIN [DWH].[JIR].[customfieldvalue] CF1 ON (CF1.issue = jis.id AND CF1.CUSTOMFIELD = 10004)
LEFT JOIN [DWH].[JIR].[customfieldvalue] CF2 ON (CF2.issue = jis.id AND CF2.CUSTOMFIELD = 10026) /*Motivo de la Averia*/
LEFT JOIN [DWH].[JIR].customfieldoption cfo1 ON (CF2.customfield = cfo1.customfield AND CF2.stringvalue=CAST(cfo1.id AS CHAR))
LEFT JOIN [DWH].[JIR].[customfieldvalue] CF3 ON (CF3.issue = jis.id AND CF3.CUSTOMFIELD = 10032)
LEFT JOIN [DWH].[JIR].[customfieldvalue] CF14 ON (CF14.issue = jis.id AND CF14.CUSTOMFIELD = 10906)
LEFT JOIN [DWH].[JIR].customfieldoption cfo8 ON (CF14.customfield = cfo8.customfield AND CF14.stringvalue=CAST(cfo8.id AS CHAR))
LEFT JOIN dwh.jir.issuetype ist ON ist.ID = jis.issuetype

ORDER BY CF1.STRINGVALUE ASC

非常感谢任何帮助或建议。非常感谢

标签： sqlsql-server

解决方案

简短的回答是否定的——这里没有“更好”。您可以应用 ISNULL 来避免输入一些内容。例子：

(ISNULL(CF3.STRINGVALUE, 'Empty') = 'Empty')

似乎为我节省了少量额外的输入。在内部，引擎会做同样多的工作。EAV 设计的世界并不是写代码的好地方。

推荐阅读