?
Solved

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

Posted on 2007-07-25
13
Medium Priority
?
1,091 Views
Last Modified: 2008-01-09
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:

SELECT *
FROM (SELECT COUNT(t1.C0)
      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.
0
Comment
Question by:mersis
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19563610
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
0
 

Author Comment

by:mersis
ID: 19563903
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?
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 19563928
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.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19563929
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

0
 

Author Comment

by:mersis
ID: 19563970
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?
0
 

Author Comment

by:mersis
ID: 19563985
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19563998
>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...
0
 
LVL 14

Assisted Solution

by:GGuzdziol
GGuzdziol earned 1000 total points
ID: 19564028
SELECT t2.*,
    (
    SELECT COUNT(c0)
      FROM (
        SELECT DISTINCT c1, c0, c3
          FROM table1 t1
      ) t1
      WHERE t1.c1 = t2.c1
    )
  FROM table2 t2;
0
 

Author Comment

by:mersis
ID: 19565718
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?
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 19565840
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.
0
 

Author Comment

by:mersis
ID: 19566399
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...
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20228852
Forced accept.

Computer101
EE Admin
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question