CODEWITHSUNDEEP
oracle11gsubquery
jalal rasooly
jalal rasooly

Reputation: 705

how to use select parameters in where clause of nested query in oracle?

suppose I have tree table

   h          y         t
-------     -----   ------------
id           id      id   name
-------     -----   ------------
1             1      1    john
2             2      2    alex
3             8      6    maggie

and I have a query like this:

select t.*,(select  y.id from (select * h where h.id > t.id) y)  t

problem is I can't use t.id in inner query. I want to know what is the problem and what is the solution? i'm using this query in oracle 11g

Upvotes: 3

Views: 1325

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can only refer to tables (or their aliases) in an outer scope across one level. So t is not in scope in the innermost level, and not recognised.

Your query will only work if there is a single h record with a higher ID than each t record anyway - which seems unlikely; otherwise the subquery will return too many rows.

You don't need nested, or any, subqueries here. You have more levels than you need anyway. For this example you can just do:

select t.*, h.id from t join h on h.id > t.id

But since your example data and query don't match up it's hard to tell what you really need.

Upvotes: 2

Related Questions