Subselect a Summed col in Oracle

Question

this is an attempted fix to a crystal reports use of 2 sub reports!

I have a query that joins 3 tables, and I wanted to use a pair of sub selects that bring in the same new table.

Here is the first of the two columns in script:

SELECT ea."LOC_ID", lo."DESCR", ea."PEGSTRIP", ea."ENTITY_OWNER"
, ea."PCT_OWNERSHIP", ea."BEG_BAL", ea."ADDITIONS", ea."DISPOSITIONS"
, ea."EXPLANATION", ea."END_BAL", ea."NUM_SHARES", ea."PAR_VALUE"
, ag."DESCR", ea."EOY", ea."FAKEPEGSTRIP",
(select sum(htb.END_FNC_CUR_US_GAAP) 
from EQUITY_ACCOUNTS  ea , HYPERION_TRIAL_BALANCE htb
where
htb.PEGSTRIP = ea.PEGSTRIP and
htb.PRD_NBR = 0 and
htb.LOC_ID = ea.LOC_ID and
htb.PRD_YY = ea.EOY 
 ) firstHyp
 FROM   ("TAXPALL"."ACCOUNT_GROUPING" ag 
 INNER JOIN "TAXP"."EQUITY_ACCOUNTS" ea 
 ON (ag."ACCT_ID"=ea."PEGSTRIP") AND (ag."EOY"=ea."EOY")) 
 INNER JOIN "TAXP"."LOCATION" lo ON ea."LOC_ID"=lo."LOC_ID"
 WHERE  ea."EOY"=2009
 ORDER BY ea."LOC_ID", ea."PEGSTRIP"

When this delivers the dataset the value of "firstHyp" fails to change by pegstrip value. It returns a single total for the join and fails to put the proper by value by pegstrip. I thought that the where clause would have picked up the joins line by line.

I don't do Oracle syntax often so what am I missing here?

TIA

Answer 1

Your SQL is equivilent to the following:

SELECT ea."LOC_ID", lo."DESCR", ea."PEGSTRIP", 
       ea."ENTITY_OWNER" , ea."PCT_OWNERSHIP", 
       ea."BEG_BAL", ea."ADDITIONS", ea."DISPOSITIONS" , 
       ea."EXPLANATION", ea."END_BAL", ea."NUM_SHARES", 
      ea."PAR_VALUE" , ag."DESCR", ea."EOY", ea."FAKEPEGSTRIP",
     (select sum(htb.END_FNC_CUR_US_GAAP) 
      from EQUITY_ACCOUNTS iea  
         Join HYPERION_TRIAL_BALANCE htb 
            On htb.PEGSTRIP = iea.PEGSTRIP
              and htb.LOC_ID = iea.LOC_ID 
              and htb.PRD_YY = iea.EOY
      where htb.PRD_NBR = 0 ) firstHyp 
FROM "TAXPALL"."ACCOUNT_GROUPING" ag 
    JOIN "TAXP"."EQUITY_ACCOUNTS" ea 
        ON ag."ACCT_ID"=ea."PEGSTRIP" 
            AND ag."EOY"=ea."EOY"
    JOIN "TAXP"."LOCATION" lo
        ON ea."LOC_ID"=lo."LOC_ID" 
WHERE ea."EOY"=2009
ORDER BY ea."LOC_ID", ea."PEGSTRIP"

Notice that the subquery that generates firstHyp is not in any way dependant on the tables in the outer query... It is therefore not a Correllated SubQuery... meaning that the value it generates will NOT be different for each row in the outer query's resultset, it will be the same for every row. You need to somehow put something in the subquery that makes it dependant on the value of some row in the outer query so that it will become a correllated subquery and run over and over once for each outer row....

Also, you mention a pair of subselects, but I only see one. Where is the other one ?

Answer 2

Use:

   SELECT ea.LOC_ID, 
          lo.DESCR, 
          ea.PEGSTRIP, 
          ea.ENTITY_OWNER, 
          ea.PCT_OWNERSHIP, 
          ea.BEG_BAL, 
          ea.ADDITIONS, 
          ea.DISPOSITIONS, 
          ea.EXPLANATION, 
          ea.END_BAL, 
          ea.NUM_SHARES, 
          ea.PAR_VALUE, 
          ag.DESCR, 
          ea.EOY, 
          ea.FAKEPEGSTRIP,
          NVL(SUM(htb.END_FNC_CUR_US_GAAP), 0) AS firstHyp
     FROM TAXPALL.ACCOUNT_GROUPING ag 
     JOIN TAXP.EQUITY_ACCOUNTS ea ON ea.PEGSTRIP = ag.ACCT_ID
                                 AND ea.EOY = ag.EOY 
                                 AND ea.EOY = 2009
     JOIN TAXP.LOCATION lo ON lo.LOC_ID = ea.LOC_ID
LEFT JOIN HYPERION_TRIAL_BALANCE htb ON htb.PEGSTRIP = ea.PEGSTRIP
                                    AND htb.LOC_ID = ea.LOC_ID 
                                    AND htb.PRD_YY = ea.EOY
                                    AND htb.PRD_NBR = 0
 GROUP BY ea.LOC_ID, 
          lo.DESCR, 
          ea.PEGSTRIP, 
          ea.ENTITY_OWNER, 
          ea.PCT_OWNERSHIP, 
          ea.BEG_BAL, 
          ea.ADDITIONS, 
          ea.DISPOSITIONS, 
          ea.EXPLANATION, 
          ea.END_BAL, 
          ea.NUM_SHARES, 
          ea.PAR_VALUE, 
          ag.DESCR, 
          ea.EOY, 
          ea.FAKEPEGSTRIP,
 ORDER BY ea.LOC_ID, ea.PEGSTRIP

I agree with Charles Bretana's assessment that the original SELECT in the SELECT clause was not correlated, which is why the value never changed per row. But the sub SELECT used the EQUITY_ACCOUNTS table, which is the basis for the main query. So I removed the join, and incorporated the HYPERION_TRIAL_BALANCE table into the main query, using a LEFT JOIN. I wrapped the SUM in an NVL rather than COALESCE because I didn't catch what version of Oracle this is for.