问题描述
我有很长的 SELECT
查询,但我已将相关部分粘贴到此处.
I have quite a long SELECT
query but I have pasted the relevant part here.
我需要将 CASE
语句的结果用于另一个 CASE
语句.我正在 SQL Server 中执行此操作.
I need to use the result of the of my CASE
statement to use in another CASE
statement. I'm doing this in SQL Server.
非常感谢您的帮助.
SELECT
CompanyContact.Name AS CompanyName,
CASE
WHEN SUBSTRING(HeadLease.TenantBreakNotice, LEN(HeadLease.TenantBreakNotice), 1) = 'M'
THEN CONVERT(VARCHAR(10), DATEADD(DD, -365 / (12 / SUBSTRING(HeadLease.TenantBreakNotice, 1, LEN(HeadLease.TenantBreakNotice) - 1)), HeadLease.TenantBreakDate), 103)
WHEN SUBSTRING(HeadLease.TenantBreakNotice, LEN(HeadLease.TenantBreakNotice), 1) = 'Y'
THEN CONVERT(VARCHAR(10), DATEADD(DD, -365 * (SUBSTRING(HeadLease.TenantBreakNotice, 1, LEN(HeadLease.TenantBreakNotice) - 1)), HeadLease.TenantBreakDate), 103)
ELSE HeadLease.TenantBreakNotice
END AS [TenantBreakNotice], <-- I need this to be used in the case statement below.
CASE
WHEN [TenantBreakNotice] < CONVERT(varchar(10), getdate(), 103)
THEN 'Expiry'
WHEN [TenantBreakNotice] IS NULL
THEN 'Expiry'
ELSE 'Break'
END AS [LeaseEventType]
FROM
HeadLease
推荐答案
不能在定义列别名的同一个 select
中使用列别名.通常的解决方案是重复逻辑(难以维护),使用子查询或 CTE.SQL Server 提供了另一种优雅的解决方案:
You cannot use a column alias in the same select
where it is defined. The usual solution is to repeat the logic (hard to maintain), use a subquery, or CTE. SQL Server offers another elegant solution:
SELECT hl.Name AS CompanyName, v.TenantBreakNotice,
(CASE WHEN v.TenantBreakNotice < CONVERT(varchar(10), getdate(), 103) THEN 'Expiry'
WHEN TenantBreakNotice IS NULL THEN 'Expiry'
ELSE 'Break'
END) AS [LeaseEventType]
FROM HeadLease hl OUTER APPLY
(VALUES (CASE WHEN SUBSTRING(hl.TenantBreakNotice, LEN(hl.TenantBreakNotice), 1) = 'M'
THEN CONVERT(VARCHAR(10), DATEADD(DAY, -365/(12/SUBSTRING(hl.TenantBreakNotice, 1, LEN(hl.TenantBreakNotice) -1)), hl.TenantBreakDate), 103)
WHEN SUBSTRING(hl.TenantBreakNotice, LEN(hl.TenantBreakNotice), 1) = 'Y'
THEN CONVERT(VARCHAR(10), DATEADD(DAY, -365*(SUBSTRING(hl.TenantBreakNotice,1, LEN(hl.TenantBreakNotice)-1)), hl.TenantBreakDate), 103)
ELSE hl.TenantBreakNotice
END) v(TenantBreakNotice);
当然,逻辑是不正确的,因为您将日期作为字符串进行比较.然而,这是你需要自己弄清楚的事情.不要将日期转换为日期操作的字符串.并且,您应该将结果输出为 YYYY-MM-DD,以便格式明确.
Of course, the logic is incorrect, because you are comparing dates as strings. However, that is something you need to figure out yourself. Don't convert dates to strings for date operations. And, you should output the results as YYYY-MM-DD so the formats are unambiguous.
这篇关于在另一个 Case 语句中使用 Case 语句的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!