WHERE子句中的无效列名错误，使用CASE选择了列 [英] Invalid column name error in WHERE clause, column selected with CASE

问题描述

我有一个(相当复杂的)SQL语句，我在其中从许多不同的表中选择数据，并且为了应对不良的旧数据结构，我有几个自定义列，它们根据其他列的值获取其值.我目前已经用CASE语句解决了这个问题:

I have a (rather complicated) SQL statement where I select data from lots of different tables, and to cope with a bad legacy data structure, I have a couple of custom columns that get their values based on values from other columns. I have currently solved this with CASE statements:

 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语句中使用条件值.如果我添加

I get all the results I expect when executing the query in MS SQL Server Management Studio, and the column names are listed as I have specified in my AS clauses. However, for some reason I'm not allowed to use the conditional values in a WHERE statement. If I add

AND ChannelValue > Limit * p.Percentage / 100

在查询结束时，我在该行上看到一条错误消息

at the end of the query, I get an error on that line saying

第207条消息，第16级，状态1，第152行
无效的列名"ChannelValue"

Msg 207, Level 16, State 1, Line 152
Invalid column name 'ChannelValue'

为什么不允许这样做?我该怎么办?

Why is this not allowed? What should I do instead?

推荐答案

使用SELECT列表中声明的别名有效的SQL语句的唯一部分是ORDER BY子句.对于查询的其他部分，您只需要重复整个CASE表达式并信任优化器即可识别出它是相同的.

The only part of the SQL Statement where it is valid to use an alias declared in the SELECT list is the ORDER BY clause. For other parts of the query you just have to repeat the whole CASE expression and trust the optimiser to recognise it is the same.

如果您使用的是SQL2005 +，则可以使用CTE来避免此问题，该问题有时有助于提高可读性.

If you are on SQL2005+ you can use a CTE to avoid this issue which sometimes helps with readability.

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

这篇关于WHERE子句中的无效列名错误，使用CASE选择了列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！