在WHERE子句中,无效的列名错误使用CASE选择的列
问题描述:
我有一个(非常复杂的)SQL语句,我从很多不同的表中选择数据,为了应对不良的遗留数据结构,我有一些自定义根据其他列的值获取其值的列。我已经与目前陈述CASE
解决了这个:在WHERE子句中,无效的列名错误使用CASE选择的列
SELECT
...,
CASE channel
WHEN 1 THEN channel_1
WHEN 2 THEN channel_2
...
ELSE 0
END AS ChannelValue,
CASE channelu
WHEN 1 THEN channelu_1
WHEN 2 THEN channelu_2
...
ELSE '0'
END AS ChannelWithUnit,
...
FROM
...
--rest of statement continues with multiple joins and where/and clauses...
我得到的所有执行在MS SQL Server Management Studio中查询时,列名被列为我在AS
子句指定我所期望的结果。但是,由于某种原因,我不允许在WHERE
语句中使用条件值。如果我在查询的末尾添加
AND ChannelValue > Limit * p.Percentage/100
,我得到一个错误在该行称
消息207,级别16,状态1,行152
无效的列名称ChannelValue“
为什么不允许这样做?我该怎么做呢?
答
SQL语句它是有效的使用在SELECT
列表中声明一个别名的只有部分是ORDER BY
条款。对于查询的其他部分,您只需重复整个CASE表达式并相信优化器识别它是相同的。
如果您使用的是SQL2005 +,则可以使用CTE来避免此问题,这有助于提高可读性。
WITH YourQuery As
(
SELECT
Limit,
Percentage,
CASE channel
WHEN 1 THEN channel_1
WHEN 2 THEN channel_2
...
ELSE 0
END AS ChannelValue,
CASE channelu
WHEN 1 THEN channelu_1
WHEN 2 THEN channelu_2
...
ELSE '0'
END AS ChannelWithUnit,
...
FROM
)
select ...
FROM YourQuery WHERE
ChannelValue > Limit * Percentage/100
答
不能在同一select
级别的where子句中使用ChannelValue
列名。
你将不得不把这整个select
在一个子查询。
select ....
from
(
your select query
) as innerSelect
where ChannelValue > Limit * p.Percentage/100
答
您可以使用CTE - 像
WITH CTE AS
(
SELECT
...,
CASE channel
WHEN 1 THEN channel_1
WHEN 2 THEN channel_2
...
ELSE 0
END AS ChannelValue,
CASE channelu
WHEN 1 THEN channelu_1
WHEN 2 THEN channelu_2
...
ELSE '0'
END AS ChannelWithUnit,
...
FROM
)
SELECT *
FROM CTE
WHERE ChannelValue > Limit * p.Percentage/100
答
-- SOMETHING FROM ADVENTURE WORKS THIS WORKS AS THE ABOVE POSTER
--- USING 'WITH CTE AS'
-- MY ANSWER TO A QUERY
WITH CTE AS
(
SELECT HE.Gender AS [GENDER], HE.HireDate AS [HIREDATE], HE.BirthDate AS [BIRTHDATE],
CASE
WHEN DATEPART(YY,[BIRTHDATE]) BETWEEN 1962 AND 1970 AND [GENDER] = 'M' AND DATEPART(YY,[HIREDATE]) > 2001 THEN 'MALE'
WHEN DATEPART(YY,[BIRTHDATE]) BETWEEN 1972 AND 1975 AND [GENDER] = 'F' AND DATEPART(YY,[HIREDATE]) BETWEEN 2001 AND 2002 THEN 'FEMALE'
ELSE 'NOTREQUIRED'
END AS [RESULT]
FROM [HumanResources].[Employee] AS HE
)
SELECT *
FROM CTE
WHERE [RESULT] <> 'NOTREQUIRED' -- GOT THIS TOO WORK NO FEMALES IN RESULT
ORDER BY [RESULT]