Showing posts with label oracle invalid identifier error. Show all posts
Showing posts with label oracle invalid identifier error. Show all posts

Saturday, May 26, 2012

Oracle Correlated Query Scope. Invalid Identifier Error

Consider query

[sourcecode language="sql"]
SELECT (SELECT tab.col  FROM DUAL) a
FROM (SELECT 1 col  FROM DUAL) tab;
[/sourcecode]

Output is

A
---
1

Here I am using tab table value inside subquery that is one level down and it's recognized by system.

Now, If I take it one more level down, that is, using tab value inside subquery's subquery, something like

[sourcecode language="sql"]
SELECT (SELECT * FROM (SELECT tab.col  FROM DUAL)) A
FROM (SELECT 1 col  FROM DUAL) tab
[/sourcecode]

It throws "invalid identifier TAB.COL" error

At Second level its not recognized by system.