return value from nested sql

Question

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"

Justin Pihony · Accepted Answer · 2012-02-21 18:42:49Z

4

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

answered Feb 21, 2012 at 18:42

Justin Pihony

67.2k20 gold badges154 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

capdragon Over a year ago

dohh! I always over complicate, that's easy. Keeping things simple takes creativity.

northpole · Accepted Answer · 2012-02-21 18:33:15Z

1

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.

answered Feb 21, 2012 at 18:33

northpole

10.3k7 gold badges38 silver badges60 bronze badges

2 Comments

capdragon Over a year ago

sorry, from the documentation, i don't see how i can use this.

northpole Over a year ago

ahhhh, I see, I was a little misdirected by your explanation. Looks like Justin provided the right solution.

Collectives™ on Stack Overflow

return value from nested sql

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related