--- In firebird-support@yahoogroups.com, Robert martin <rob@...> wrote:

>
> Hi
>
> We have a table called StockLevel that had 1 record per item, per area
> (Region). We have expanded this so that an item can have multiple
> entries for a given region. However I am struggling to come to grips
> with how to rewrite a large number of SQLs that have used this table.
>
> An example might be
>
> Select Tablea.x, Tablea.y, Tableb.z, StockLevel.ShelfQty
> From Tablea
> JOIN Tableb on .....
> JOIN StockLevel ON (StockLevel.ItemRef = :ItemRef AND
> StockLevel.RegionRef = :RegionRef)
>
>
> These have all worked fine, however now we want the SUM of ShelfQty for
> the specified region. Is it possible to re write the SQL above to

do this?

Have you tried:

Select Tablea.x, Tablea.y, Tableb.z, sum(StockLevel.ShelfQty)
From Tablea
JOIN Tableb on .....
JOIN StockLevel ON (StockLevel.ItemRef = :ItemRef AND
StockLevel.RegionRef = :RegionRef)
Group By Tablea.x, Tablea.y, Tableb.z

I am a bit confused about how your stock level relates to tablea or
tableb. The join conditions purely reference the StockLevel table and
do not define any sort of relationship. Even though there is
technically no difference between the on condition and where condition
for an inner join (there *IS* a difference for an outer join so it may
be appropriate there), it is best for readability sake to separate the
join conditions (sticky tape) from the rest. If I understand correctly
your goal, I would write query like:

Select Tablea.x, Tablea.y, Tableb.z, sum(StockLevel.ShelfQty)
From Tablea
JOIN Tableb on .....
JOIN StockLevel ON 1=1
WHERE StockLevel.ItemRef = :ItemRef
AND StockLevel.RegionRef = :RegionRef
Group By Tablea.x, Tablea.y, Tableb.z

Adam