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

Question

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

What you are trying to do.? Why you are using so many nested queries? — uhs
– uhs, Commented Jun 7, 2015 at 7:49
Your example is at least missing a from, and the data implies y is a separate table, not just an inline view. Can you clarify the example? Is there a reson you're using subqueries instead of joins - do you think it's necessary for your real, presumably more complicated, query? — Alex Poole
– Alex Poole, Commented Jun 7, 2015 at 9:34

Alex Poole · Accepted Answer · 2015-06-07 09:31:54Z

2

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.

answered Jun 7, 2015 at 9:31

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related