ORA-00904: invalid identifier in nested subquery

Question

Suppose, we have query like this:

SELECT 
tt.id first_column,
tt.value second_column,
--another values
qq.code_1 third_column,
qq.code_2 column4,
qq.code_3 column5,
qq.code_4 column6,
qq.code_5 column7

FROM
test_tbl tt LEFT JOIN
(SELECT * FROM (
  SELECT id, code_value, ROW_NUMBER() OVER (ORDER BY code_value) AS RN
  FROM attributes attr WHERE attr.id = tt.id AND 
      attr.code IN ('ATTRIBUTE_CODE_1',
                    'ATTRIBUTE_CODE_2',
                    'ATTRIBUTE_CODE_3',
                    'ATTRIBUTE_CODE_4',
                    'ATTRIBUTE_CODE_5')
)
PIVOT (MAX(code_value) FOR (RN) IN (1 as code_1, 2 as code_2, 3 as code_3, 4 as code_4, 5 as code_5))) qq
ON tt.id = qq.id
-- LEFT JOINS with another tables...

The problem is error:

ORA-00904: "TT"."ID": invalid identifier

How can I circumvent this obstacle?

Of course above example is easy and dummy, real query is more complicated.

This example can be checked here: http://sqlfiddle.com/#!4/eec83/3

score 1 · Accepted Answer · answered Sep 19 '15 at 00:43

Just remove the problematic condition:

WHERE attr.id = tt.id

... but to ensure correct RN values, you'll also need to add a partition by id inside your row_number() over clause.

ROW_NUMBER() OVER (PARTITION BY id ORDER BY code_value) AS RN

SQLFiddle Demo:

SELECT 
tt.id first_column,
tt.value second_column,
--another values
qq.code_1 third_column,
qq.code_2 column4,
qq.code_3 column5,
qq.code_4 column6,
qq.code_5 column7

FROM
test_tbl tt LEFT JOIN
(SELECT * FROM (
  SELECT id, code_value, ROW_NUMBER() OVER (partition by id ORDER BY code_value) AS RN
  FROM attributes attr WHERE
      attr.code IN ('ATTRIBUTE_CODE_1',
                    'ATTRIBUTE_CODE_2',
                    'ATTRIBUTE_CODE_3',
                    'ATTRIBUTE_CODE_4',
                    'ATTRIBUTE_CODE_5')
)
PIVOT (MAX(code_value) FOR (RN) IN (1 as code_1, 2 as code_2, 3 as code_3, 4 as code_4, 5 as code_5))) qq
ON tt.id = qq.id

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

You can't refer the other tables/views in an inline view. I couldn't find a documentation which states this, but that's just it. It's by design, how query optimizer treat the query.

The actual case is more obvious with this simple query:

SELECT 
  *
FROM
  test_tbl tt , 
  ( 
    SELECT ID, CODE_VALUE
    FROM attributes attr 
    WHERE attr.id = tt.id
    ) qq

Error:

ORA-00904: "TT"."ID": invalid identifier

If you want to do join then you should use JOIN and put the condition in ON clause or put the condition in WHERE clause to join inline view and the table. Like here:

SELECT 
  *
FROM
  test_tbl tt , 
  ( 
    SELECT ID, CODE_VALUE
    FROM attributes attr 
    ) qq 
WHERE attr.id = tt.id

or see if you want to make a CROSS APPLY. It seems like this is a join.

Also see this answer, there is a nice expression:

An inline view / derived table creates a temporary unnamed view at the beginning of your query and then treats it like another table until the operation is complete. Because the compiler needs to create a temporary view when it sees on of these subqueries on the FROM line, those subqueries must be entirely self-contained with no references outside the subquery.

I believe that's not always true, in some cases oracle can choose to merge inline view. And can be forced to be merged by use of NO_MERGE optimizer hint.

SEE THIS

I intend to add query plan as soon as I can, which may give more idea

score 0 · Answer 3 · answered Sep 17 '15 at 08:25

You can remove that condition from the inner sub query, because it does not any significance as you already have left outer join on both table on its common column id.

You can make below changes to your query.

SELECT 
tt.id first_column,
tt.value second_column,
qq.code_1 third_column,
qq.code_2 column4,
qq.code_3 column5,
qq.code_4 column6,
qq.code_5 column7
FROM
test_tbl tt LEFT JOIN
(SELECT * FROM (
  SELECT id, code_value, ROW_NUMBER() OVER (ORDER BY code_value) AS RN
  FROM attributes attr WHERE 
     attr.code IN ('ATTRIBUTE_CODE_1',
                'ATTRIBUTE_CODE_2',
                'ATTRIBUTE_CODE_3',
                'ATTRIBUTE_CODE_4',
                'ATTRIBUTE_CODE_5')
)
PIVOT (MAX(code_value) FOR (RN) 
IN (1 as code_1, 2 as code_2, 3 as code_3, 4 as code_4, 5 as code_5))) qq
ON tt.id = qq.id;

Here is the your Updated SQL Fiddle

May this will help you.

Unfortunately, for other data that is not effective. Look at this: http://sqlfiddle.com/#!4/ac236d/2 — David Silva, Sep 17 '15 at 12:15

ORA-00904: invalid identifier in nested subquery

3 Answers3