CODEWITHSUNDEEP
sqlsql-server
Tomas Kubes
Tomas Kubes

Reputation: 25108

SQL Invalid column name - computed condition in WHERE clause

I have following query:

SELECT ImgHeight, ImgWidth,
 IIF(ImgHeight > ImgWidth, ImgHeight, ImgWidth) as imgMaxSize
 FROM Images
WHERE imgMaxSize > 100

But I am getting message:

Invalid column name 'imgMaxSize'.

I could duplicate the condition as following, but it doesn't seem to be effective to compute the maximum twice. 

SELECT Img1Height,
 Img1Width,
 IIF(Img1Height > Img1Width, Img1Height, Img1Width) as imgMaxSize
 From Realty
where IIF(Img1Height > Img1Width, Img1Height, Img1Width) > 100

What is the recommended approach here?

Upvotes: 0

Views: 793

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In SQL Server, you can use outer apply for this:

SELECT i.ImgHeight, i.ImgWidth, v.imgMaxSize
FROM Images i OUTER APPLY
     (VALUES(CASE WHEN i.ImgHeight > i.ImgWidth THEN i.ImgHeight ELSE i.ImgWidth END)
     ) v(imgMaxSize)
WHERE v.imgMaxSize > 100;

Of course, CTEs and subqueries also solve the problem; I just happen like this use of lateral joins.

Or:

SELECT i.ImgHeight, i.ImgWidth,
       (CASE WHEN i.ImgHeight > i.ImgWidth THEN i.ImgHeight ELSE i.ImgWidth END) as imgMaxSize
FROM Images i 
WHERE i.ImgHeight > 100 or i.ImgWidth > 100;

I should add that I'm also quite biased toward ANSI standard syntax unless there is a good reason to use something else. Hence, outer apply has a good reason. IIF() instead of CASE is not a good reason.

Upvotes: 3

Anand
Anand

Reputation: 1123

This:

select * from (
    SELECT ImgHeight, ImgWidth,
        IIF(ImgHeight > ImgWidth, ImgHeight, ImgWidth) as imgMaxSize
    FROM Images
    ) as i
WHERE imgMaxSize > 100

Or:

with cte as (
    SELECT ImgHeight, ImgWidth,
        IIF(ImgHeight > ImgWidth, ImgHeight, ImgWidth) as imgMaxSize
    FROM Images
);

select * from cte
WHERE imgMaxSize > 100

Upvotes: 1

Related Questions