Invalid Column Name Error In Where Clause, Column Selected With 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

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
         WHEN1THEN channel_1
         WHEN2THEN channel_2
         ...
         ELSE0ENDAS ChannelValue,
     CASE channelu
         WHEN1THEN channelu_1
         WHEN2THEN channelu_2
         ...
         ELSE'0'ENDAS 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 
         WHEN1THEN channel_1 
         WHEN2THEN channel_2 
         ... 
         ELSE0ENDAS ChannelValue, 
     CASE channelu 
         WHEN1THEN channelu_1 
         WHEN2THEN channelu_2 
         ... 
         ELSE'0' ENDAS ChannelWithUnit, 
     ... 
 FROM  
)
SELECT * 
FROM CTE
WHERE ChannelValue > Limit * p.Percentage / 100

Solution 4:

-- SOMETHING FROM ADVENTURE WORKS THIS WORKS AS THE ABOVE POSTER--- USING 'WITH CTE AS'-- MY ANSWER TO A QUERYWITH CTE AS
 (
 SELECT HE.Gender AS [GENDER], HE.HireDate AS [HIREDATE],      HE.BirthDate AS [BIRTHDATE],
CASEWHEN DATEPART(YY,[BIRTHDATE]) BETWEEN1962AND1970AND [GENDER] ='M'AND DATEPART(YY,[HIREDATE]) >2001THEN'MALE'WHEN DATEPART(YY,[BIRTHDATE]) BETWEEN1972AND1975AND [GENDER] ='F'AND DATEPART(YY,[HIREDATE]) BETWEEN2001AND2002THEN'FEMALE'ELSE'NOTREQUIRED'ENDAS [RESULT]
FROM [HumanResources].[Employee] AS HE
)
SELECT*FROM CTE
WHERE [RESULT] <>'NOTREQUIRED'-- GOT THIS TOO WORK NO FEMALES IN RESULTORDERBY [RESULT]