Where clause dependent on column being populated by a `case select` statement postresql

Question

I'm writing a query where I'm doing a union on several different tables to combine them to make one item.

Let's make up an example where I have a table Purchases and a table Leases and I'm creating a new result for Sales. On one of the tables, I'm basing the Description based on a different column. Ex:

SELECT
   u.id as UserId,
   p.name as Name,
   p.email as Email,
   'Purchase' as Table,
   p.id as SaleId,
   p.status_id as SaleStatusId,
   p.description as SaleDescription
FROM users u
INNER JOIN purchase p on u.id = p.user_id

UNION ALL

SELECT
   u.id as UserId,
   l.name as Name,
   l.email as Email,
   'Lease' as Table,
   l.id as SaleId,
   l.status_id as SaleStatusId,
   CASE
       WHEN l.status_id = 2 THEN 'Finalized Lease'
       ELSE 'Pending Lease'
   END as SaleDescription
FROM users u
INNER JOIN lease l on u.id = l.user_id

This is all fine and dandy, but the problem comes when I'm trying to search on this description I'm adding. So I'm trying to add where clauses like:

SELECT
   u.id as UserId,
   p.name as Name,
   p.email as Email,
   'Purchase' as Table,
   p.id as SaleId,
   p.status_id as SaleStatusId,
   p.description as SaleDescription
FROM users u
INNER JOIN purchase p on u.id = p.user_id
WHERE (@searchDescription = {} or p.description = any (@searchDescriptions))

UNION ALL

SELECT
   u.id as UserId,
   l.name as Name,
   l.email as Email,
   'Lease' as Table,
   l.id as SaleId,
   l.status_id as SaleStatusId,
   CASE
       WHEN l.status_id = 2 THEN 'Finalized Lease'
       ELSE 'Pending Lease'
   END as SaleDescription
FROM users u
INNER JOIN lease l on u.id = l.user_id
WHERE (@searchDescription = {} or SaleDescription = any (@searchDescriptions))

searchDescription is a list of strings that if any match the SaleDescription, I want it returned. All others ignored.

So the first WHERE statement works fine because it's searching on an existing column. The second WHERE does NOT work, since it can't search a column that hasn't "been populated yet", and I get this error:

There is a column named `SaleDescription` in table "*SELECT* 1", but it cannot be referenced from this part of the query

So basically I'm wondering how I can achieve this? How can I add a where clause to a column that gets populated by a case select?

** Note this is dumby code, there may be errors-- I haven't tested it

Answer 1

CTE could be used to select columns needed way, then do filtering on CTE, like:

;
WITH my_union_sales AS (
    SELECT
       u.id as UserId,
       p.name as Name,
       p.email as Email,
       'Purchase' as Table,
       p.id as SaleId,
       p.status_id as SaleStatusId,
       p.description as SaleDescription
    FROM users u
    INNER JOIN purchase p on u.id = p.user_id
    
    UNION ALL
    
    SELECT
       u.id as UserId,
       l.name as Name,
       l.email as Email,
       'Lease' as Table,
       l.id as SaleId,
       l.status_id as SaleStatusId,
       CASE
           WHEN l.status_id = 2 THEN 'Finalized Lease'
           ELSE 'Pending Lease'
       END as SaleDescription
    FROM users u
    INNER JOIN lease l on u.id = l.user_id
) SELECT * 
FROM my_union_sales
WHERE (@searchDescription = {} or SaleDescription = any (@searchDescriptions));