How to Search values Integer column alias in a WHERE Clause 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?
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&lt;>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. ...