How to use select parameters in where is a subquery clause in oracle?
Suppose I have a 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 request like this:
select t.*,(select y.id from (select * h where h.id > t.id) y) t
The problem is that in an inner query I cannot use t.id
. I want to know what is the problem and what is the solution? I am using this query in oracle 11g
source to share
You can only reference tables (or their aliases) in outer space at the same level. Thus, t
it is not in scope at the innermost level and is not recognized.
Your query will only work if there is one record h
with a higher ID than each record t
, which seems unlikely; otherwise, the subquery will return too many rows.
You don't need nested or any sub-queries here. You have more levels than you need. For this example, you can simply:
select t.*, h.id from t join h on h.id > t.id
But since your sample data and query don't match, it's hard to tell what you really need.
source to share