CLAIM_ID and others are unique, so I don't have to worry about the sum. I've had to break the summations into a few selects in order to make sure I don't get multiple counts.
I'm abandoning this line of thought because one of the simple summations that I could combine with the outer select wouldn't gain much, it's a simple select on a FK so it should be very quick. Combining one of the other slower queries would make the code unmaintainable, losing whatever benefit I would get from performance. It's easier to throw hardware at something then programmers.

Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Friday, September 16, 2005 2:56 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: One grouped left join or two queries?

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 ...





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links