I have a stored procedure that returns several aggregations:

FOR SELECT ... INTO :A, :B, :C
DO BEGIN
SELECT ... SUM(G)... WHERE X = :A INTO :D;
SELECT ... SUM(H)... WHERE Y = :A INTO :E;
SELECT ... SUM(I)... WHERE Z = :A INTO :F;

/* return A, B, C, D, E, and F */
SUSPEND;
END


I was thinking of combining one of the aggregations with the outer
select using a left join.

Does anyone have a SWAG as to the expense of a join with grouping and
coalesce as compared to two selects?

FOR SELECT
rg.CLAIM_ID, rg.DATESBM, rg.DECIMALQTY
FROM
V_CLAIM_REPLENISHGRP rg
JOIN TMP_GPI g
ON g.NDC = rg.PRODUCTID
WHERE
rg.RPL_GRP = :RPL_GRP AND
rg.NDC9 = :NDC9 AND
g.GPI = :GPI AND
g.TEE = :TEE AND
g.UNIT_DOSE_USE = :DOSE AND
(g.UNIT_DOSE_USE NOT IN ('U') OR g.PKG_SZ = :DOSE_SZ)
INTO
:CLAIM_ID, :DATESBM, :DECIMALQTY
DO BEGIN
...
BO_QTY = 0;
/* buyouts */
SELECT
SUM(cbo.QTY_CLAIM)
FROM
RPL_CLM_BUYOUT_ITM cbo
WHERE
cbo.CLAIM_ID = :CLAIM_ID
INTO
:BO_QTY;

would become

FOR SELECT
rg.CLAIM_ID, rg.DATESBM, rg.DECIMALQTY, COALESCE(SUM(cbo.QTY_CLAIM),
0)
FROM
V_CLAIM_REPLENISHGRP rg
JOIN TMP_GPI g
ON g.NDC = rg.PRODUCTID
LEFT JOIN RPL_CLM_BUYOUT_ITM cbo
ON cbo.CLAIM_ID = rg.CLAIM_ID
WHERE
rg.RPL_GRP = :RPL_GRP AND
rg.NDC9 = :NDC9 AND
g.GPI = :GPI AND
g.TEE = :TEE AND
g.UNIT_DOSE_USE = :DOSE AND
(g.UNIT_DOSE_USE NOT IN ('U') OR g.PKG_SZ = :DOSE_SZ)
GROUP BY
rg.CLAIM_ID, rg.DATESBM, rg.DECIMALQTY
INTO
:CLAIM_ID, :DATESBM, :DECIMALQTY
DO BEGIN
...
... other summations remain ...