asked on

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)

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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)

Qlemo

The group by is superfluous in each of the correlated subqueries.
I would definitely prefer the first query of sdstuber.