OraFAQ Forum: SQL & PL/SQL » inner query use outer query

Home » SQL & PL/SQL » SQL & PL/SQL » inner query use outer query (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

inner query use outer query [message #636402]

Wed, 22 April 2015 15:03

miroconnect@yahoo.com
Messages: 202
Registered: April 2006

Senior Member



select 
*
from (
select 
(select 'col1' from dual  where  1=dp_params.days_pending  ) as col1,
(select sum(day) from (

select '1'  as day from dual  /*where 1=dp_params.days_pending*/ 
union
select '2'  as day from dual  /*where 1=dp_params.days_pending*/
)
) as col2  
from dual qu,
(select 1 as days_pending from dual )  dp_params  )

this is my query , I trying to explain my problem using simple dual select, In reality all the simple dual selects are complex one.

(select sum(day) from (

select 1  as day from dual  /*where 1=dp_params.days_pending*/ 
union
select 2  as day from dual  /*where 1=dp_params.days_pending*/
)
) as col2

in this inner query I want to use the dp_params.days_pending , is there any way I can refer to days_pending in inner query? I commented out where condition which needs filed from outer query

Report message to a moderator

Re: inner query use outer query [message #636406 is a reply to message #636402]

Wed, 22 April 2015 22:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> WITH dp_params 
     AS (SELECT 1 AS days_pending 
         FROM   dual) 
SELECT * 
FROM   (SELECT (SELECT 'col1' 
                FROM   dual 
                WHERE  1 = dp_params.days_pending) AS col1, 
               (SELECT SUM(day) 
                FROM   (SELECT '1' AS day 
                        FROM   dual /*where 1=dp_params.days_pending*/ 
                        UNION 
                        SELECT '2' AS day 
                        FROM   dual /*where 1=dp_params.days_pending*/ 
                       ))                          AS col2 
        FROM   dual qu, 
               dp_params) 
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17  
SQL> /

COL1       COL2
---- ----------
col1          3

Report message to a moderator