Invalid identifier in an Oracle SQL query

Question

I'm kind of a beginner in programming and I can't succeed in running this query on Oracle SQL.

What I would like to achieve is selecting the id and the names (aya_id, aya_name) from a table AYANTDROIT, and for each of these ids another name which is linked to the id in another table, BENEFICE. The problem is, in my query on the table benefice, I need to get back the id selected in the first line, and no matter how I always end up with an

ORA-00904 : invalid identifier : aya.aya_id

Here is my code:

SELECT DISTINCT aya.aya_id, 
aya_name,
(SELECT aya_name
FROM AYANTDROIT aya2 inner join  
     (SELECT ben_aya_id,
      level lev,
      ben_ben_id
      FROM benefice 
      START WITH ben_aya_id = **aya.AYA_ID**
      CONNECT BY prior ben_ben_id = ben_aya_id
      ORDER BY lev desc
      )
 on aya2.aya_id = ben_ben_id
 where rownum = 1),
FROM AYANTDROIT aya
ORDER BY aya_name

However, when I submit this following query, aya.aya_id does not return any error.

SELECT DISTINCT aya.aya_id,
  aya_name,
  (SELECT aya_name 
   FROM AYANTDROIT aya2 
   WHERE aya2.aya_id = 
      (SELECT ben_ben_id 
       FROM benefice LEFT OUTER JOIN ayantdroit ayad 
                      ON ben_aya_id = ayad.aya_id 
                      WHERE ayad.aya_id = **aya.AYA_ID**
      )
   )
FROM AYANTDROIT aya
ORDER BY aya_name

Would anyone know why I can call this aya_id in the second case and not in the first? It would be really helpful here :)

Thanks all for your time and have a nice day!

1. You have a syntax error in your first query with an unwanted `,` after `where rownum = 1)`. 2. Looks like a possible duplicate of this: http://stackoverflow.com/questions/5247698/sql-nested-subquery-referencing-grandparents-column — Sameer Mirji, Feb 01 '16 at 10:40
1) Thanks for the syntax error, it was a problem due to a poor copy/paste :) — Jérémy Thizy, Feb 01 '16 at 10:58
2) Indeed it is the same kind of problem, thanks for the link. Unfortunately, since my query is created in a java code in Eclipse in an application and directly run into the database, I can't use their solution to solve my problem...will have to keep searching ! Thanks for your help :) — Jérémy Thizy, Feb 01 '16 at 11:04

score 0 · Accepted Answer · answered Feb 01 '16 at 11:04

The problem is that aya.aya_id is too deeply nested in correlated subquery. There are several ways to rebuild your query, here is one:

select distinct a1.aya_id, a1.aya_name, a2.aya_name
  from ayantdroit a1
  left join (
    select connect_by_root(ben_aya_id) root, ben_ben_id
      from benefice where connect_by_isleaf = 1
      start with ben_aya_id in (select aya_id from ayantdroit)
      connect by prior ben_ben_id = ben_aya_id) b
  on a1.aya_id = b.root
  left join ayantdroit a2 on a2.aya_id = b.ben_ben_id

Please read also similiar question on Asktom site.

Indeed, the problem came from the depth of the nested subquery that I did not know how to bypass (its really too bad that you can't do that with Oracle !). Thanks for bringing the connect by that I did not know, it seems like it's working now with your solution ! Thanks again, Jérémy — Jérémy Thizy, Feb 01 '16 at 12:42

Invalid identifier in an Oracle SQL query

1 Answers1