Hi Rick!

I would guess these two approaches would need a similar amount of time
to execute (the left join would force the dataset order in the plan),
but you'd better try for yourself or wait for someone who knows the
answer to reply, than to simply believe in my guesses ;o)

The reason for me answering, is to ask you another question. Are you
certain that the two approaches give identical answers? If rg.CLAIM_ID
is unique in the first select then they will, and you may continue to
investigate your left join solution. However, if rg.CLAIM_ID may be
repeated due to the same CLAIM_ID having different rg.DATESBM, rg.
DECIMALQTY or g.NDC, then using LEFT JOIN will double SUM(cbo.
QTY_CLAIM) if there are two rg.CLAIM_ID, triple if three etc.

Set

--- In firebird-support@yahoogroups.com, "Rick Debay" wrote:
> 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 ...