Oracle: How to alias subquery and use it in WHERE with IN?

Question

Why is this not working?

WITH ids AS (SELECT unique id FROM table1 WHERE cd = :cd AND
                                                yr = :yr)
SELECT NVL(person_id, NULL) FROM table2 WHERE id IN ids

In my actual code, the statement ids is used twice. I made this post to find out how to alias subquery and then use it in WHERE along with IN, as seen above.

I get the error : Warning: oci_execute(): ORA-00904: "IDS": invalid identifier

NVL(person_id, NULL)?

David Aldridge
– David Aldridge

2013-12-07 18:37:02 +00:00
Commented Dec 7, 2013 at 18:37 — David Aldridge
– David Aldridge, Commented Dec 7, 2013 at 18:37

Hart CO · Accepted Answer · 2013-12-06 22:44:21Z

2

The cte is like a subquery, but it's not exactly the same, as your case illustrates. IN accepts a list of values or a subquery.

This should work fine:

SELECT NVL(person_id, NULL) 
FROM table2 
WHERE id IN (SELECT unique id 
             FROM table1 WHERE cd = :cd AND yr = :yr)

Update: You don't need to (can't) alias the subquery used with the IN operator, if you want to use the cte, you need a subquery that references it:

;WITH ids as (SELECT unique id 
              FROM table1 
              WHERE cd = :cd AND  yr = :yr)
SELECT NVL(person_id, NULL) 
FROM table2 WHERE id IN (SELECT * FROM ids)

edited Dec 6, 2013 at 22:44

answered Dec 6, 2013 at 22:39

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

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

2 Comments

George Newton Over a year ago

I had that originally, but I tried to alias the latter statement as in above. There is a reason I want to alias it, as that statement is twice in my code. I made this post to find out how to alias a subquery and then use that subquery in WHERE. Thank you.

George Newton Over a year ago

Thank you for explaining that such a feat is not possible. In accordance with Stackoverflow's Friendly Exchange policy, I will upvote your answer by 1 and accept your answer in t - 1 minutes. Thank you ever so much.

Collectives™ on Stack Overflow

Oracle: How to alias subquery and use it in WHERE with IN?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related