Home » SQL & PL/SQL » SQL & PL/SQL » reffering to a column from parent query in inner query
reffering to a column from parent query in inner query [message #316899] Mon, 28 April 2008 18:02 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
here i my sql

select nga_id , can_number   ,(select phase_id from nga_base where nga_id=nc.NGA_id) as [b]super_phase_id[/b] ,
   (select sum(AMT_ACTION_FIN_ASST) 
             from nga_can nc_inn 
            where 
            nc_inn.can_number= nc.can_number 
            and
            nc_inn.NGA_ID in (select nga_id from nga_base   where phase_id  =[b]super_phase_id[/b]) 
            ) as prior_funding                                           
    from nga_can nc   where nga_id=143


in this query i cannot access super_phase_id in the inner query why not ?and is there a way ?



[mod-edit: code tags added; next time please add them yourself]

[Updated on: Mon, 28 April 2008 19:08] by Moderator

Report message to a moderator

Re: reffering to a column from parent query in inner query [message #316903 is a reply to message #316899] Mon, 28 April 2008 18:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above.
reffering to parent query column [message #316915 is a reply to message #316899] Mon, 28 April 2008 19:45 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
is there any restriction in referring to a column in a select clause inner query
like can I access a column in any level of inner query ?
like

select a , (select b from a_table where b in
(select b from c where c.b=xyz.b )
) as b

from xyz

this is just an example to express my question .
Re: reffering to parent query column [message #316938 is a reply to message #316915] Mon, 28 April 2008 21:44 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No.

You are using scalar-sub-queries (SELECTs that return a single value) in the SELECT clause. Each of these has its own scope - they cannot reference each other.

Scalar-subqueries are a bad idea anyway - see this article.

Rewrite your query as a join instead.

Ross Leishman
Previous Topic: Oracle Maestro
Next Topic: sql tuning
Goto Forum:
  


Current Time: Sun Dec 11 00:14:57 CST 2016

Total time taken to generate the page: 0.11797 seconds