CODEWITHSUNDEEP
sqlpostgresql
capdragon
capdragon

Reputation: 14899

return value from nested sql

I have the following SQL and i want theorder_item_wo_id column to return null if the item does not exist in the nested sql and return the value of a column oi.cop_workorder_id if it does.

How can i accomplish this?

SELECT 
    cop.wo_id,
    CASE
     WHEN EXISTS (SELECT oi.cop_workorder_id 
            FROM oseo_orderitem  oi
            WHERE oi.cop_workorder_id = cop.wo_id) THEN 
            oi.cop_workorder_id
     ELSE null
    END AS order_item_wo_id
FROM oseo_cop_wo cop
where cop.wo_id = '123';

It looks like the oi.cop_workorder_id does not get recognized outside of the nested sql.

ERROR: missing FROM-clause entry for table "oi"

Upvotes: 0

Views: 143

Answers (2)

Justin Pihony
Justin Pihony

Reputation: 67115

Unless I am missing something, it seems you are overcomplicating this. Just use a LEFT JOIN:

SELECT 
    cop.wo_id,
    oi.cop_workorder_id AS order_item_wo_id
FROM oseo_cop_wo AS cop
    LEFT JOIN oseo_orderitem AS oi
        ON oi.cop_workorder_id = cop.wo_id
WHERE cop.wo_id = '123';

Upvotes: 4

northpole
northpole

Reputation: 10346

use can use the COALESCE function. It works similar to Oracle's NVL function. I don't have PostgreSQL available to write and test this, however.

Upvotes: 1

Related Questions