At 09:12 AM 17/10/2007, you 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)

This statement doesn't have any join criteria. To join tables you
have to match keys between the tables. So, for this statement to
make sense, you would need

Select Tablea.x, Tablea.y, Tableb.z, StockLevel.ShelfQty
From Tablea
JOIN Tableb on .....
JOIN StockLevel ON
StockLevel.ItemRef = Sometable.ItemRef
AND
StockLevel.RegionRef = Sometable.RegionRef
WHERE
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?

With corrected SQL, yes, why not? SUM() is an aggregate function, so
you would use a GROUP BY clause to determine the aggregation.

In a GROUP BY clause you have to include all of the contributing
fields in your FROM specification (apart from the actual aggregate
expression). The rightmost field in the GROUP BY clause determines
the level at which the aggregation is done. So, using the example
above, stripping out possible non-participants,

Select
Sometable.xxx,
Stocklevel.RegionRef
SUM(StockLevel.ShelfQty)
From Sometable
JOIN StockLevel ON
StockLevel.ItemRef = Sometable.ItemRef
AND
StockLevel.RegionRef = Sometable.RegionRef
WHERE
StockLevel.ItemRef = :ItemRef
AND
StockLevel.RegionRef = :RegionRef
GROUP BY 1,2

Since each output record represents the aggregation of multiple
records, it's not logically possible to include any fields that are
outside the scope of the grouping. If you have any old SQL
statements in your apps that survived from IB 5.6 days, which let you
get away with such naughtiness without complaining, then you will
need to fix them.

>I have written a Stored procedure to return the Sumed quantities for a
>given itemref, regionref combination. But I am not sure it helps for
>examples like the aboe.

Can't comment. If you're not sure it helps, then I'm not sure either. :-)

./heLen