PostgreSQL Case with a subquery using a different where clause

Question

I am trying to query the correct price by going through and choosing between 2 prices.

These are in order of importance from highest to lowest:

if [tran_type] is equal to Quot than choose this price
if [default_level] is greater than 0, going to be a number from 1-8

The [default_level] is a place holder with a numerical value which is used to signify a pricing level from 1 to 8.

The price when [default_level] is greater than 0 is stored as the blank [start_cust_or_group] in the same table and [level_] is the value it is tied with for the 1-8 pricing level.

So say if [default_level] is 6 then the correct price I need pulled is for [start_cust_or_group] to equal blank [level_] would be 6 and the latest date available [start_date]

item_or_pricecode	uom_code	tran_type	default_level	level_	price
27MIETE11K	EA		6	0	0.00
22RGRD45BS	CT	Quot		0	144.10

Below is what I would like the final outcome to be.

For the first item (27MIETE11K) it sees tran_type was blank then it moved on and found default_level was 6 so it chooses Level_ where its 6 and start_cust_or_group is blank and it takes that price. For the next item (22RGRD45BS) it sees tran_type is Quot so it populates the price in the LevelResult. This part of the query with Quot works.

item_or_pricecode	uom_code	LevelResult
27MIETE11K	EA	29.57
22RGRD45BS	CT	144.10

SELECT 
    [item_or_pricecode], [uom_code], 
    CASE 
        WHEN [tran_type] <> '' THEN [price] 
        WHEN [default_level] > 0 THEN XXXXXX
        ELSE 0 
    END AS LevelResult 
FROM
    [test].[tablepricing].[pricing] 
WHERE 
    [start_cust_or_group] = '7000192' 
    AND [cust_shipto_num] = '2' 
    AND [RowDeleted] = 0 
ORDER BY 
    [start_date] DESC

That code works if I put [default_level] but that just returns the value for default_level in the LevelResult column.

I need to have a select query where I put XXXXXX. I don't know how to pass the [default_level] to the select query to match with the [level_] or if it's even possible.

SELECT 
    x.[item_or_pricecode], x.[uom_code], x.[level_], x.[price],x.[start_date]
FROM 
    (SELECT
         x.[item_or_pricecode], x.[uom_code], x.[level_], x.[price],x.[start_date],
         ROW_NUMBER() OVER (PARTITION BY x.[item_or_pricecode] ORDER BY x.[start_date] DESC, x.[level_] ASC) AS rn
     FROM 
         [test].[tablepricing].[pricing] AS x
     WHERE
         [start_cust_or_group] = '' 
         AND [RowDeleted] = 0  
         AND [item_or_pricecode] = '27MIETE11K') AS x 
WHERE 
    x.rn <= 8

item_or_pricecode	uom_code	level_	price	start_date
27MIETE11K	EA	1	38.65	2024-01-15
27MIETE11K	EA	2	34.81	2024-01-15
27MIETE11K	EA	3	33.05	2024-01-15
27MIETE11K	EA	4	31.43	2024-01-15
27MIETE11K	EA	5	30.49	2024-01-15
27MIETE11K	EA	6	29.57	2024-01-15
27MIETE11K	EA	7	28.97	2024-01-15
27MIETE11K	EA	8	28.39	2024-01-15

This is a separate query to show the level prices and how I got it to find the latest date for each entry.

Normally if I just did

WHERE item_or_pricecode = '27MIETE11K' 
  AND start_cust_or_group = ''

on the pricing table I would get 78 entries with older prices mixed in.

Piece of advice: if you have nested queries (like in your second query), I'd recommend not to use the same alias (here: x) in the inner and outer scope. That makes for really difficult and confusing reading of your code, and can lead to very unexpected and hard to diagnose problems at times. Use something useful, meaningful, and use a different alias for each level in your query — marc_s
– marc_s, Commented Dec 6, 2024 at 6:32
You wrtite ORDER BY x.[start_date] DESC - what is this column? pls, add to data source example. Also about [start_cust_or_group] = '' — ValNik
– ValNik, Commented Dec 6, 2024 at 11:00
it won't fit i had to remove rows because it spills over to the next line on the forum so I can't the [start_date] column is formatted just like 2024-05-28 00:00:00.000 — Siege
– Siege, Commented Dec 6, 2024 at 18:14
Do you have 1 table pricing or more? Please show table (tables), data example and desired output. — ValNik
– ValNik, Commented Dec 9, 2024 at 18:15
i did that's those 2 tables at the top i'm not gonna put the whole table cause there are like 50 columns that aren't needed. it's just one table [test].[tablepricing].[pricing] — Siege
– Siege, Commented Dec 9, 2024 at 18:40

Toni · Accepted Answer · 2025-11-19 07:44:09Z

For each row in your main query if tran_type = 'Quot', return that row’s own price. Else if default_level > 0, return the latest price from the same table. Else return 0. Code should be:

SELECT
    p.item_or_pricecode,
    p.uom_code,
    CASE
        WHEN p.tran_type = 'Quot' THEN p.price
        WHEN p.default_level > 0 THEN (
            SELECT lp.price
            FROM test.tablepricing.pricing lp
            WHERE lp.item_or_pricecode = p.item_or_pricecode
              AND lp.uom_code = p.uom_code
              AND lp.start_cust_or_group = ''
              AND lp.level_ = p.default_level
              AND lp.RowDeleted = 0
            ORDER BY lp.start_date DESC
            LIMIT 1
        )
        ELSE 0
    END AS LevelResult
FROM test.tablepricing.pricing p
WHERE p.start_cust_or_group = '7000192'
  AND p.cust_shipto_num = '2'
  AND p.RowDeleted = 0
ORDER BY p.start_date DESC;

Collectives™ on Stack Overflow

PostgreSQL Case with a subquery using a different where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related