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)
"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)
DatabasesOracle DatabaseSQL
Last Comment
Qlemo
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
The group by is superfluous in each of the correlated subqueries.
I would definitely prefer the first query of sdstuber.
I would definitely prefer the first query of sdstuber.
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)