Accessing Outer Select Field From Subquery

[getjbb]

Hello

I am trying to do a subquery within a select statement that access the field from the outer select. Is that possible? I tried it and it did not work. Below is I hope a simple example of what I am trying to do.


SELECT table1.field1 alias1,
table1.field2 alias2,
table1.field3 alias3,
(SELECT table2.field1
FROM table2
WHERE table2.field2 = <value from field2 of the outer Select statement>
AND table2.field3 = <value from field3 of the outer Select statement>
AND table2.field4 = 'OPEN'
) alias4,
table1.field4 alias5,
( ( (SUM(DECODE(field6,9, amount, 0) ) ) +
(SUM(DECODE(field6,10, amount, 0) ) )
) -
( (SUM(DECODE(field6,15, amount, 0) ) ) +
(SUM(DECODE(field6,17, amount, 0) ) ) +
(SUM(DECODE(field6,18, amount, 0) ) ) +
(SUM(DECODE(field6,19, amount, 0) ) )
)
) alias6
FROM table1
GROUP by table1.field1, table1.field2, table1.field3, table1.field4;

The subquery needs to send back only one row due to the Group by.

Thanks

getjbb

 

[SantaMufasa]

...I tried it and it did not work...

Unfortunately, yours is a pretty broad "error message". It will help us to help you if you can post the "ORA-xxxxx: <Error message>" that you are receiving.


The short answer is, yes, you should be able to access outer fields in a "correlated subquery", but since your subquery is "in-line" in the expression list, the GROUP BY is expecting the subquery expression to be a GROUP expression, as well.

So, let's look at your error, then we can do some troubleshooting.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[getjbb]

SantaMufasa,

I get the following error:


---------------------------
Select 1 Results (PA) - ISQL Session 1
---------------------------
Select error: ORA-01427: single-row subquery returns more than one row

---------------------------

---------------------------

getjbb

 

[slaing]

The message is telling you that the "SELECT table2.field1 FROM table2 ... " statement isn't returning one row.

Oracle needs a 'rectangular' grid of data in its results set. I'm strugglng to think of a way of putting it into a nice neat sentence, so here's an example or two that should explain it !

Start with :
select user from dual;

Returns 1 row as expected. Now something add in a select akin to what you're doing :

select user, (select username from v$session) from dual

This gives a 1427 error like yours. Adding a GROUP BY user has no effect because Oracle doesn't like the results set before it even gets to the grouping stage.

If we force the embedded select to return 1 row then it works
select user, (select max(username) from v$session) from dual;

However, that doesn't give you an exact solution - you need to work out what will give you the single row answer you want in the embedded select.

Looking at the overall query, couldn't you restructure it to do a normal join ?


Steve

 

[SantaMufasa]

GetJBB, Steve is certainly correct in his analysis. Since we are attempting to assist you with a hypothetical/contrived set of sample syntax (thus unclear on what you are really trying to do), we are attempting to get your SQL running at a syntactical/run-time level while your challenge may be at a fundamentally logical level.

I believe we need to step back an re-visit your fundamental need. Could you please display a simplified sample of the output you want along with a mapping back to the source table? With that, perhaps we can suggest an alternate SQL statement to achieve your objective without trying to rework the syntax of a piece of code that may be logically spurious from the outset.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[getjbb]

SELECT Apno, Org, Pr_yr, Projno,
(
( (SUM(DECODE(btyp,9, bal, 0) ) ) +
(SUM(DECODE(btyp,10, bal, 0) ) )
) -
( (SUM(DECODE(btyp,15, bal, 0) ) ) +
(SUM(DECODE(btyp,17, bal, 0) ) ) +
(SUM(DECODE(btyp,18, bal, 0) ) ) +
(SUM(DECODE(btyp,19, bal, 0) ) )
)
) Amount
FROM bud_sum
GROUP by Apno, Org, Pr_yr, Projno;

The outer select should give me back a row grouped by the fields
in the Group By Clause:

Apno Org Pr_yr Projno Amount

42011 101000000000000 1000 002370 0
42011 101000000000000 2000 0 8147.79
42011 101000000000000 2000 001000 1069
42011 101000000000000 1998 0 0
42011 101000000000000 1999 0 5345.62
42011 101000000000000 2001 0 139436.62
42011 101000000000000 2001 001010 4000
42011 101000000000000 2001 001030 0
42012 305000000000000 1999 0 31726229.02
42012 305000000000000 2001 0 30834580.65
42012 305000000000000 2001 003342 8927992.12
and so on....

The field, Org,is not what I really need. The field Org should be used
to retrieve the field Oca, which is in the cost_center table. Again to
get the needed field, Oca, I will be mapping the two tables bud_sum and
cost_center by using org and appno_no in the
Where clause (cost_center.Org = bud_sum.Org and cost_center.Apno = bud_sum.Apno).

When selecting Org, Apno, oca from cost_center the following
result is returned:

Org Apno Oca(What I want to get)

101000000000000 42011 110011
101000000000000 42017 110017
101000000000000 42016 110016
101000000000000 42011 110011
101000000000000 42014 110014
102000000000000 42011 120011
102000000000000 42011 120011
102000000000000 42017 120017
102000000000000 42014 120014
102000000000000 42014 120014
103000000000000 42014 130014
103000000000000 42011 130011
103000000000000 42011 130011
103000000000000 42016 130016
103000000000000 42015 130015
103000000000000 42017 130017
103000000000000 42014 130014
104000000000000 42016 140016
104000000000000 42011 140011
104000000000000 42011 140011;

As mentioned in my previous message the subquery select will
be like: select Oca
from cost_center
where cost_center.Org = bud_sum.Org
and cost_center.Apno = bud_sum.Apno
and cost_center.Stat_cd = 'A'
and cost_center.Del_ind = 'N'
in place of selecting Org.

I hope the above will give you a better understanding of solution I am trying to reach.


getjbb

 

[slaing]

Are you expecting the subquery to return 1 row ?

From the sample data you show, a given apno and org will always return the same oca.

If that's always true then using 'SELECT DISTINCT oca ... ' in your subquery should do the job.

If that's not true, then please post another sample results set showing how you expect different oca to be grouped

Steve

(still can't see why you don't use a simple join between bud_sum and cost_center - am I missing the point ?)

 

[getjbb]

slaing,

I did a simple join between bud_sum and cost_cent and it worked. I guess I attempted to do a subquery when a join was all that I needed. Thanks.

getjbb

 

[getjbb]

I meant to thank every one for their input.

getjbb