SQL第二个病例陈述关闭第一个病例陈述的列
问题描述:
我正在用两个Case When语句编写查询,第二个使用第一个的状态来确定值。但是,第二条语句中出现无效列错误。有谁知道解决这个问题的简单方法吗?谢谢!SQL第二个病例陈述关闭第一个病例陈述的列
select
a.ID
,ss.Date
,ss.Name
,ss.Payload
,case when statusdescription = 'Bad Request' then 'Not Resolved' Else 'Resolved' End as [Error Status]
,case when
[Error Status] = 'Not Resolved' --Invalid Column issue occurring with [Error Status] here
then 'No Resolution' Else a.Date End as [Date],
,GETDATE() [Insert Date]
from #Errors a
join Table1 ss on a.id = ss.Id and a.Date = ss.Date
order by a.Date desc
答
我会在第二个case表达式中嵌套第一个case表达式。
select
a.ID
,ss.Date
,ss.Name
,ss.Payload
,case when statusdescription = 'Bad Request' then 'Not Resolved' Else 'Resolved' End as [Error Status]
,case when
case when statusdescription = 'Bad Request' then 'Not Resolved' Else 'Resolved' End = 'Not Resolved' --Invalid Column issue occurring with [Error Status] here
then 'No Resolution' Else a.Date End as [Date],
,GETDATE() [Insert Date]
from #Errors a
join Table1 ss on a.id = ss.Id and a.Date = ss.Date
order by a.Date desc
+0
看起来不错,谢谢! – AndrewC10
答
在第二CASE
表达,你可以再次检查的statusdescription
值:
SELECT
a.ID,
ss.Date,
ss.Name,
ss.Payload,
CASE WHEN statusdescription = 'Bad Request'
THEN 'Not Resolved' ELSE 'Resolved' END AS [Error Status],
CASE WHEN statusdescription = 'Bad Request'
THEN 'No Resolution' ELSE a.Date END AS [Date],
GETDATE() AS [Insert Date]
FROM #Errors a
INNER JOIN Table1 ss
ON a.id = ss.Id AND
a.Date = ss.Date
ORDER BY a.Date DESC
注意,如果该查询不会运行,您可能需要转换a.Date
你的第二个内的部分文本CASE
表达。
case _expressions_,not _statements _... – jarlh
我刚刚做了一个我自己的测试(Sql Server 2014),并且我能够从后续引用前面的CASE列,因此有可能(在SQL Server 2014中)。 –