How to Search values Integer column alias in a WHERE Clause

Question

How to Filter values in where clause alias variable,

CREATE TABLE BASE_Chq
(
ChequeInvoicePaymentId BIGINT NULL,
ChequeNumber VARCHAR(45) NOT NULL,
Amount MONEY NOT NULL
)

Once i select the whole table value i'm getting the result like below,

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
BASE_Chq

i want to filter Cheque Type wise but i'm getting error,

ERROR CODE:

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount  
FROM BASE_Chq
WHERE (ChequeType = 'PARTY')

How to search using alias column in where clause?

Answer 1

You cannot use an alias in your where clause

Without questioning any parts of the query, you could do this:

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount  
FROM BASE_Chq
WHERE (IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') = 'PARTY');

A better approach would be directly filtering on the IS NOT NULL:

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount  
FROM BASE_Chq
WHERE ChequeInvoicePaymentId IS NOT NULL;

A small DB<>Fiddle example

---

Why

The filter in the where clause can be passed to the table as a (seek) predicate before computing the IIF function in the SELECT.

More info on this processing order in an answer by Erland Sommarskog:

You can't do that because a SELECT statement is computed in this order:

FROM-JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

So when the WHERE clause is evaluated, the aliases defined in the SELECT list are not defined yet. ...