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

keenns
May 14, 2023
157 views
1 vote
2 Answers

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

Answers

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));

The reason you get that error is due to the logical [order of] query processing which is different to the order of clauses required by syntax rules. Whilst a "select query" starts with "select" according to syntax; it is NOT the first clause to be executed. The clauses that get the data (FROM & JOIN) and then filter that data (WHERE) are the first set of clauses to get executed.

What this means to your problem is that the column aliases established under the select clause are "not known" until the select clause is considered, and ths comes AFTER the where clause. Hence you get the type of error you encountered. (nb. This column alias not known problem is true for Postgres and many other RDBMS, but not all.)

/*
    Problem, the column alias "saledescription" is not understood 
    in the where clause of a single select statement.
    this is due to the logical processing order of SQL clauses:
    1.FROM 2.WHERE 3.GROUP BY 4.HAVING 5.SELECT
*/
SELECT
      u.id AS UserId
    , l.name AS Name
    , l.email AS Email
    , 'Lease' AS TABLE
    , 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 SaleDescription IN ('Finalized Lease')

> ERROR:  column "saledescription" does not exist
> LINE 16: WHERE SaleDescription IN ('Finalized Lease')

To solve this there are several methods:

/*
  method 1, repeat the case expression in the where clause
*/
SELECT
      u.id AS UserId
    , l.name AS Name
    , l.email AS Email
    , 'Lease' AS TABLE
    , 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 CASE WHEN l.status_id = 2 THEN 'Finalized Lease' 
           ELSE 'Pending Lease' END  
      IN ('Finalized Lease')

userid	name	email	table	saledescription
1	John Doe	[email protected]	Lease	Finalized Lease
3	Bob Johnson	[email protected]	Lease	Finalized Lease
5	Mike Brown	[email protected]	Lease	Finalized Lease

/*
  method 2, use a "derived table" (nested subquery)
*/
SELECT *
FROM (
    SELECT u.id AS UserId
        ,l.name AS Name
        ,l.email AS Email
        ,'Lease' AS TABLE
        ,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
    ) AS derived
WHERE SaleDescription IN ('Finalized Lease')

userid	name	email	table	saledescription
1	John Doe	[email protected]	Lease	Finalized Lease
3	Bob Johnson	[email protected]	Lease	Finalized Lease
5	Mike Brown	[email protected]	Lease	Finalized Lease

/*
  method 3, use a common table expression (CTE)
*/
WITH CTE
AS (
    SELECT u.id AS UserId
        ,l.name AS Name
        ,l.email AS Email
        ,'Lease' AS TABLE
        ,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 CTE
WHERE SaleDescription IN ('Finalized Lease')

userid	name	email	table	saledescription
1	John Doe	[email protected]	Lease	Finalized Lease
3	Bob Johnson	[email protected]	Lease	Finalized Lease
5	Mike Brown	[email protected]	Lease	Finalized Lease

fiddle

Please signup or login to give your own answer.

Click here to cancel reply.

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

Answers