Using parameters from select statement inside case statement

Question

I have the following query

SELECT product.*,
(SELECT bid FROM server1 WHERE product_id = product.id) AS bid1,
(SELECT bid FROM server2 WHERE product_id = product.id) AS bid2,
CASE
    WHEN bid1 > bid2 THEN '1 wins'
    WHEN bid2 > bid1 THEN '2 wins'
    ELSE 'undecided'
END AS bid_status
FROM product
WHERE id= $1

This query throws error in CASE statement saying bid1 column does not exist. How do I pass in the sub-query as a variable for the case statement?

score 2 · Accepted Answer · answered Jul 06 '18 at 08:17

Scope issue. Wrap your query up as a derived table first:

select dt.*
    CASE
        WHEN bid1 > bid2 THEN '1 wins'
        WHEN bid2 > bid1 THEN '2 wins'
        ELSE 'undecided'
    END AS bid_status
from
(
SELECT product.*,
    (SELECT bid FROM server1 WHERE product_id = product.id) AS bid1,
    (SELECT bid FROM server2 WHERE product_id = product.id) AS bid2,
FROM product
WHERE id= $1
) dt

Using parameters from select statement inside case statement

1 Answers1