oracle 11g alias to query

1
votes

When migrating from oracle 10g to 11g, I've a stored procedure with a syntax like:

select * from table1
union
select t,y from
(select * from table2)aliasQuery
where t=aliasQuery.t

The query works well with 10g but 11g returns an error that aliasQuery is not defined.

Maybe this syntax is no longer supported in 11g or there is some missing database configurations?

EDIT

The full query is:

select * 
  from table1 
 union 
select t, y 
  from ( select * 
          from table2 ) aliasQuery 
 where ( select max(t)
           from ( select t 
                    from table3
                   where table3.t = aliasQuery.t)
                 )>10
2
sqloracleoracle10goracle11g
This syntax works fine in 11g. You say it's part of a stored procedure, can you provide all the code? Also, can you provide the exact error returned? - John Doyle
select * from table1 union select t,y from (select * from table2)aliasQuery where (select max(t)from (select t from table 3 where table3. t=aliasQuery.t) - Hadad
error : invalid identifier aliasQuery.t - Hadad
@Hadad, I've added your posted query to the question. As you can see it doesn't make sense there is no condition in the where. Are you sure this is correct? - Ben
Sure there is a condition but i've missed it i'll update the question - Hadad

2 Answers

3
votes

Based upon the Ask Tom article "Is there some sort of nesting limit for correlated subqueries?" it would appear that correlated subquery aliases working more than one level down was a bug that was fixed. It would seem that your 10g database does not have the bug fix while your 11g does. I tried your query on my 10g database and it fails with the same error as on my 11g one: ORA-00904: "ALIASQUERY"."T": invalid identifier.

You're going to have to change the query around a bit to get it working in 11g now.

2
votes

I can confirm that this doesn't work. I get the same error as you:

select t, y 
  from ( select 1 t, 2 y 
          from dual ) aliasQuery 
 where ( select max(t)
           from ( select t 
                    from (select 1 as t from dual) table3
                   where table3.t = aliasQuery.t)
                 )>10

But this does:

select t, y 
  from ( select 1 t, 2 y 
          from dual ) aliasQuery 
 where ( select max(t)
           from (select 1 as t from dual) table3
          where table3.t = aliasQuery.t
        )>10

Translated to your query, you would have to rewrite it as such:

select * 
  from table1 
 union 
select t, y 
  from ( select * 
          from table2 ) aliasQuery 
 where ( select max(t)
           from table3 -- no need to add yet another nested select here
          where table3.t = aliasQuery.t)
       )>10

I couldn't tell you why your syntax wouldn't work any longer. It looks OK to me. Apparently the scope of the table-rename does no longer reach a doubly nested select, which I find a bit scary!