Can I refrence an IIF value in a where clause

I am writing a report in Excel that Queryies data from an ODBC table.

I wanted to create a new column for a search criteria, and use it in my where clause. for example:

SELECT 
SO_Site,
SO_Customer,
IIF((SO_PERFORM_DATE)<GetDate(),'Backlog','Normal') AS 'ISBACKLOG'

FROM WAREHOUSE.FAKESALESORDES

WHERE SO_Site=1234 AND ISBACKLOG='Backlog'

The aim of this was to return backlog orders, without a super messy where clause, so others can interpret this query in the future.

Any tips would be very helpful!

Answer

You can’t reference a named alias in its own WHERE like that.

You simply need to put the whole IIF in the WHERE statement the same as it is in the SELECT

SELECT 
    SO_Site,
    SO_Customer,
    IIF((SO_PERFORM_DATE)<GetDate(),'Backlog','Normal') AS 'ISBACKLOG'

FROM 
    WAREHOUSE.FAKESALESORDES

WHERE 
    SO_Site=1234 
    AND IIF((SO_PERFORM_DATE)<GetDate(),'Backlog','Normal')='Backlog'

Although looking at the conditions within the IIF you can simplify this even further by just looking where the date is less than GETDATE()

SELECT 
    SO_Site,
    SO_Customer,
    IIF((SO_PERFORM_DATE)<GetDate(),'Backlog','Normal') AS 'ISBACKLOG'

FROM 
    WAREHOUSE.FAKESALESORDES

WHERE 
    SO_Site=1234 
    AND SO_PERFORM_DATE<GetDate()