Tutorial :Invalid column name error in WHERE clause, column selected with CASE



Question:

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 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

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

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


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   


Solution:4

-- 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]  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »