Oracle 10G -- simple subquery ?

Below works OK for one record, but fails with
"single-row subquery returns more than one row"
when removing "and IDENTITY = 'ABC123'"

How can I get the attached by
changing my below SUPP_AGE_ANALYSIS_REP
table logic on Oracle 10G ?

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

SELECT
  distinct IDENTITY,
  PARTY_NAME,
--------------------------------
(
  SELECT
  sum(OPEN_DOM_AMOUNT) as OPEN_AMOUNT
  from SUPP_AGE_ANALYSIS_REP
  where RESULT_KEY = (select max(RESULT_KEY) from SUPP_AGE_ANALYSIS_REP)
  and INVOICE_DATE < SYSDATE - 30
  and IDENTITY = 'ABC123'
  group by IDENTITY
) as History,
--------------------------------
(
  SELECT
  sum(OPEN_DOM_AMOUNT) as OPEN_AMOUNT
  from SUPP_AGE_ANALYSIS_REP
  where RESULT_KEY = (select max(RESULT_KEY) from SUPP_AGE_ANALYSIS_REP)
  and INVOICE_DATE between SYSDATE - 30 and SYSDATE - 23
  and IDENTITY = 'ABC123'
  group by IDENTITY
) as WK_01,
--------------------------------
(
  SELECT
  sum(OPEN_DOM_AMOUNT) as OPEN_AMOUNT
  from SUPP_AGE_ANALYSIS_REP
  where RESULT_KEY = (select max(RESULT_KEY) from SUPP_AGE_ANALYSIS_REP)
  and INVOICE_DATE between SYSDATE - 6 and SYSDATE
  and IDENTITY = 'ABC123'
  group by IDENTITY
) as WK_02
-------------------------------
from SUPP_AGE_ANALYSIS_REP
where RESULT_KEY = (select max(RESULT_KEY) from SUPP_AGE_ANALYSIS_REP)
finance_teacherAsked:
Who is Participating?
 
sdstuberCommented:
this might be functionally different but should be more efficient
if party_name and identity are 1-to-1


  SELECT identity,
         party_name,
         SUM(CASE WHEN invoice_date < SYSDATE - 30 THEN open_dom_amount END) AS history,
         SUM(CASE WHEN invoice_date BETWEEN SYSDATE - 30 AND SYSDATE - 23 THEN open_dom_amount END)
             AS wk_01,
         SUM(CASE WHEN invoice_date BETWEEN SYSDATE - 6 AND SYSDATE THEN open_dom_amount END)
             AS wk_02
    FROM supp_age_analysis_rep
   WHERE result_key = (SELECT MAX(result_key) FROM supp_age_analysis_rep)
GROUP BY identity, party_name
0
 
sdstuberCommented:
this should be functionally identical to what you started with  but much more expensive than my previous post


SELECT DISTINCT
       identity,
       party_name,
       --------------------------------
       (  SELECT SUM(open_dom_amount) AS open_amount
            FROM supp_age_analysis_rep
           WHERE result_key = x.result_key AND invoice_date < SYSDATE - 30 AND identity = x.identity
        GROUP BY identity)
           AS history,
       --------------------------------
       (  SELECT SUM(open_dom_amount) AS open_amount
            FROM supp_age_analysis_rep
           WHERE result_key = x.result_key
             AND invoice_date BETWEEN SYSDATE - 30 AND SYSDATE - 23
             AND identity = x.identity
        GROUP BY identity)
           AS wk_01,
       --------------------------------
       (  SELECT SUM(open_dom_amount) AS open_amount
            FROM supp_age_analysis_rep
           WHERE result_key = x.result_key
             AND invoice_date BETWEEN SYSDATE - 6 AND SYSDATE
             AND identity = x.identity
        GROUP BY identity)
           AS wk_02
  -------------------------------
  FROM supp_age_analysis_rep x
 WHERE result_key = (SELECT MAX(result_key) FROM supp_age_analysis_rep)
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The group by is superfluous in each of the correlated subqueries.
I would definitely prefer the first query of sdstuber.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.