1

Using the below as an example:

WITH
    V_TBL1 (ID,NAME) AS (SELECT ID,NAME FROM TABLE1),
    V_TBL2 (TIME) AS (SELECT TIME FROM TABLE2 WHERE TABLE2.ID IN (V_TBL1.ID))
    
    SELECT * FROM V_TBL2
;

Why does Oracle throw the error?

-- *V_TBL1.ID: Invalid Identifier* line 3

I'm sure I've used this before and it's worked, I can't see what I'm doing wrong this time.

2

The IN predicate accepts either an explicit list of values or a subquery (returning a single column in this case).

In your query, the right side of IN is a list consisting of a single value, where the value is a reference. The reference is invalid because V_TBL1 is undefined in that context. If you want to check that TABLE2.ID is included in the V_TBL1 set, you have to use the subquery syntax:

SELECT TIME FROM TABLE2 WHERE TABLE2.ID IN (SELECT V_TBL1.ID FROM V_TBL1)

I'm sure I've used this before and it's worked

I believe your memory is failing you there, that syntax cannot possibly have worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.