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

How is it possible to referer to tables that are defined in the FROM clause, which is outside the scope of a nested select. Here is an example:

      FROM table1 AS t1
      WHERE t1.C1 = ***t2.C1***) subquery,
      table2 AS t2

the thing designated with stars is what Oracle does not like. But it is perfectly legitimate in Postgresql or SQL Server.
Is there a way around it? completely rewriting the query using inner joins, etc. is not a solution in my case. The query really ought to have the structure of a nested select.
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>but it actually leads to different results for the COUNT, which in my case is wrong.
as you actually join later with the where sq.G = t1.C1, this should give the same results actually...
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this syntax:

      FROM table1 AS t1
      WHERE t1.C1 = t2.C1) count_value
FROM table2 AS t2
mersisAuthor Commented:
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:
                     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?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

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

no rows selected

SQL> select id, (select count(*) from (select * from t t2 where = from t t1;
select id, (select count(*) from (select * from t t2 where = 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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

mersisAuthor Commented:
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?
mersisAuthor Commented:
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.
GGuzdziolConnect With a Mentor Commented:
SELECT t2.*,
      FROM (
        SELECT DISTINCT c1, c0, c3
          FROM table1 t1
      ) t1
      WHERE t1.c1 = t2.c1
  FROM table2 t2;
mersisAuthor Commented:
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.
mersisAuthor Commented:
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.

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.