CODEWITHSUNDEEP
sqloracle-database
George Newton
George Newton

Reputation: 3293

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

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

Upvotes: 0

Views: 164

Answers (1)

Hart CO
Hart CO

Reputation: 34774

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)

Upvotes: 2

Related Questions