How inside a nested select to refer to tables defined outside the nested select?

please try this syntax:

SELECT t2.* , (SELECT COUNT(t1.C0)
      FROM table1 AS t1
      WHERE t1.C1 = t2.C1) count_value
FROM table2 AS t2

yes, I know that works. But I need to have my nested select statement as part of the from clause of the outer select. The reason for that is because the actual query is a bit more complicated.
Here is another example to give you a better idea:
SELECT t2.*, (SELECT COUNT(C0)
                     FROM (SELECT DISTINCT t1.C0 AS C0, t1.C3 AS C1
                                 FROM table1 AS t1
                                 WHERE t1.C1 = t2.C1) subquery)
FROM table2 AS t2
Again, as said, works for Postgres and SQL Server, but not Oracle. How can I make it for Oracle too?

Oracle ,,sees'' identifiers only one level nesting:

SQL> select id, (select count(*) from t t2 where t1.id = t2.id) from t t1;

no rows selected

SQL> select id, (select count(*) from (select * from t t2 where t1.id = t2.id)) from t t1;
select id, (select count(*) from (select * from t t2 where t1.id = t2.id)) from t t1
                                                           *
ERROR at line 1:
ORA-00904: "T1"."ID": invalid identifier

You have to avoid it somehow - i.e. rewrite Your query to use one-level nesting.

I see, oracle only sees "one" level in the subselects indeed

solution: use CTE :

WITH subquery as ( SELECT DISTINCT t1.C0 AS C0, t1.C3 AS C1, t1.C1 G FROM table1 AS t1)
SELECT t1.*, ( select count(*) from subquery sq where sq.G = t1.C1 )
FROM table1 AS t1

GGuzdziol, its exactly my problem. How to overcome that limitation. Isn't there a way of dragging a value from one level to another to make it visible for the following level?

angelIII, your example is not going to work for me as you are having SELECT DISTINCT of *three* columns, whereas I need SELECT DISTINCT of *two*(!) columns. It may appear only a slight difference, but it actually leads to different results for the COUNT, which in my case is wrong.

angelIII, I agree it should be the same actually, but in fact there is a different problem:

for both of these solutions the problem is if the level of nestedness is not 2 but say 10, i.e. there are 10 levels of wrapping one select into another. I know it may sound impossible, but thats the way we use it in SQL Server and Postgres. Now the problem with the proposed solutions is that these "aggregate" columns will have to be pulled up all 10 levels up. While this is not impossible, it is clearly less nice and I suspect worse in terms of performance, as the intermediate tables will be bigger in size.

Isn't there any yet simpler solution?

There is no
allow_huge_nesting = TRUE
initialization parameter for init.ora.

As for intermediate tables...the performance may be worse, may be not - You don't know unless You test and benchmark it.

Reimplementing it to use different techniques may be an option for You. If I got You correctly You have one piece of code that is supposed to work on SQL Server, Postgres and Oracle. This means it doesn't make use of dbms-specific features of each database. This is generally bad idea and I wouldn't implement anything this way. I would prefer to have as much instances of application as much dbms's it is supposed to work with.

GGuzdziol, you almost got me right.
I do not want to run exactly the same SQL on all those platforms, but SQL that I would like to run is machine generated SQL and therefore some common patterns would hugely simplify life. So its not one off query that I could write and then be happy with it. I need to cover all cases: one level of nestedness, 2, 3, 10, etc...

Forced accept.

Computer101
EE Admin