Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Using columns in nested queries (Read 4799 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Using columns in nested queries
01.12.07 at 13:36:28
 
Using columns in nested queries
 
Nested queries seem to only be possible to go 1 level deep. Consider the following SQL
 
select 1
,      ( select 2
        from   dual d2
        where  d2.dummy = d1.dummy
      )
from   dual d1`
 
This is a valid SQL statement where a column value of the outer select statement (table d1) is used
inside the inner table (table d2).
 
You cannot do this however when you go 2 levels deep. Consider the following SQL:
Here table d1 is referenced 2 levels deep (it is compared to table d3). This is failing
with an ORA-00904: d1.dummy invalid identifier.
 
select 1  
,      ( select 2
        from   dual d2
        ,      ( select 3  
                 from   dual d3
                 where  d3.dummy = d1.dummy
               )
        where d2.dummy = d1.dummy
      )
from   dual d1
 
If d1.dummy is replaced by a literal it works but that just shows that the nesting of  
statements works. We prove above that you cannot reference a table more than 1 level deep.
 
select 1  
,      ( select 2
        from   dual d2
        ,      ( select 3  
                 from   dual d3
                 where  d3.dummy = 'a'
               )
        where d2.dummy = d1.dummy
      )
from   dual d1
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
nuukem
YaBB Newbies
*


I Love SQL*XL

Posts: 0
Re: Using columns in nested queries
Reply #1 - 03.02.09 at 05:24:02
 
I'm running into this exact situation.  I realize this is a very old post but I was wondering if there was a solution to a double nested "select".  
 
Code:
select 1  
,      ( select 2
        from   dual d2
        ,      ( select 3  
                 from   dual d3
                 where  d3.dummy = d1.dummy
               )
        where d2.dummy = d1.dummy
      )
from   dual d1  


 
Is there any way to link d1.dummy in the second nested select (d3.dummy = d1.dummy)?  I can't define a literal in my situation.
 
Also, forgot to mention that I'm using Oracle 10g.
 
Thanks very much!
 
Phil
Back to top
 
« Last Edit: 03.02.09 at 05:35:21 by nuukem »  
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Using columns in nested queries
Reply #2 - 03.02.09 at 21:18:23
 
I have not tried it but may the following work?
 
Code:
select 1 as a
,	( select 2, d1.a as a
	  from   dual d2
	  ,	( select 3  
		     from   dual d3
		     where  d3.dummy = d2.a
		   )
	  where d2.dummy = d1.a
	)
from   dual d1   

Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1