I have 5 tables which are set up at SQLFiddle
I need assistance in refining the below query. Currently it gives me "ORA-01427: single-row subquery returns more than one row"
Db - Oracle 11.x
sum(CRED) - sum(DEB) of 'Insurer' records only; group by pol.SP_NUM
select sp_num as pol_#,
(coalesce (sum(
(
select sum(Q.AMTQ) AS INSD
from S_INVOICE debit
, S_ASSET pol
, S_QUOTE_ITEM Q
where debit.FN_ACCNT_ID = pol.row_id
and pol.x_quote_id = Q.row_id
and debit.DEBIT_TYPE = 'Insurer'
and debit.CO_ID is null
UNION ALL
select sum(QXM.AMTQXM) AS INSD
from S_INVOICE debit
, S_ASSET pol
, S_QUOTE_ITEM_XM QXM
where debit.FN_ACCNT_ID = pol.row_id
and debit.DEBIT_TYPE = 'Insurer'
and debit.CO_ID = QXM.ROW_ID
)
),0) -
coalesce (sum(
(
select sum(Q.AMTQ) AS INSC
from S_SRC_PAYMENT credit
, S_ASSET pol
, S_QUOTE_ITEM Q
where credit.ASSET_ID = pol.row_id
and pol.x_quote_id = Q.row_id
and credit.CG_DEDN_TYPE_CD = 'Insurer'
and credit.CO_ID is null
UNION ALL
select sum(QXM.AMTQXM) AS INSC
from S_SRC_PAYMENT credit
, S_ASSET pol
, S_QUOTE_ITEM_XM QXM
where credit.asset_id = pol.row_id
and credit.CG_DEDN_TYPE_CD = 'Insurer'
and credit.CO_ID = QXM.ROW_ID
)
),0)
) as NP
from s_asset
group by sp_num;
Expected O/P:
sum(cred) - sum(deb) = (412) - (63.1) = 348.9
The revised query I came up with is:
WITH CREDITS AS (SELECT SP_NUM, SUM(INSC) AS TOTAL_CREDITS
FROM (select pol.SP_NUM, Q.AMTQ AS INSC
from S_SRC_PAYMENT credit,
S_ASSET pol,
S_QUOTE_ITEM Q
where credit.ASSET_ID = pol.row_id and
pol.x_quote_id = Q.row_id and
credit.CG_DEDN_TYPE_CD = 'Insurer' and
credit.CO_ID is null
UNION ALL
select pol.SP_NUM, QXM.AMTQXM AS INSC
from S_SRC_PAYMENT credit,
S_ASSET pol,
S_QUOTE_ITEM_XM QXM
where credit.asset_id = pol.row_id and
credit.CG_DEDN_TYPE_CD = 'Insurer' and
credit.CO_ID = QXM.ROW_ID)
GROUP BY SP_NUM),
DEBITS AS (SELECT SP_NUM, SUM(INSD) AS TOTAL_DEBITS
FROM (select pol.SP_NUM, Q.AMTQ AS INSD
from S_INVOICE debit,
S_ASSET pol,
S_QUOTE_ITEM Q
where debit.FN_ACCNT_ID = pol.row_id and
pol.x_quote_id = Q.row_id and
debit.DEBIT_TYPE = 'Insurer' and
debit.CO_ID is null
UNION ALL
select pol.SP_NUM, QXM.AMTQXM AS INSD
from S_INVOICE debit,
S_ASSET pol,
S_QUOTE_ITEM_XM QXM
where debit.FN_ACCNT_ID = pol.row_id and
debit.DEBIT_TYPE = 'Insurer' and
debit.CO_ID = QXM.ROW_ID)
GROUP BY SP_NUM)
SELECT COALESCE(c.SP_NUM, d.SP_NUM) AS POL_#,
COALESCE(TOTAL_CREDITS, 0) - COALESCE(TOTAL_DEBITS, 0) AS NP
FROM CREDITS c
FULL OUTER JOIN DEBITS d
ON d.SP_NUM = c.SP_NUM;
The basic problem was that the internal queries (here pulled out as CTE's) were summing the INSC and INSD values, but weren't preserving the SP_NUM (policy number) as part of the summation (i.e. there was no GROUP BY). Also, because I don't know your data and thus couldn't be certain that each policy number would have at least a credit or a debit I made it a FULL OUTER JOIN. If you can be certain that there will always be either a credit or a debit to a policy number you could put that CTE first in the final SELECT and the LEFT OUTER JOIN the other table. In any case, this works.
Best of luck.