Invalid column name error in WHERE clause, column selected with CASE
Solution 1
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.
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
Solution 2
You cannot use ChannelValue
column name in the where clause at the same select
level.
You will have to put this whole select
in a subquery.
select ....
from
(
your select query
) as innerSelect
where ChannelValue > Limit * p.Percentage / 100
Solution 3
You can use a CTE - something like
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
Tomas Aschan
I am an engineering physicist from Stockholm, Sweden, with a passionate interest in programming and software architecture. Since creating my first program at age 12 (a VB6 app that showed a smiley when a button was clicked) I've spent many hours in front of my computer, watching screen casts and reading blogs about programming as well as trying all the new concepts out in my own programs. With a Master's degree in Engineering Physics from the Royal Institute of Technology in Stockholm, Sweden, I have deepened my modelling and reasoning skills, as well as had the opportunity to try out many different technologies and tools. I am currently working as a software engineer at Spotify, mostly massaging data to enable our internal research into developer productivity.
Updated on July 24, 2022Comments
-
Tomas Aschan almost 2 years
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...
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 aWHERE
statement. If I addAND ChannelValue > Limit * p.Percentage / 100
at the end of the query, I get an error on that line saying
Msg 207, Level 16, State 1, Line 152
Invalid column name 'ChannelValue'Why is this not allowed? What should I do instead?