richardfry, the reason our query does work has to do with the order of certain processes. The Where clause is processed first and operates on a single row. The Group By is processed much later and it summarizes 1 to many rows down to 1 row per unique value(s). So at the time the Where clause is processing, the value of SUM(SORDERS.QTY) has not yet been determined.
I don't know anything about running queries from Excel so I don't know if either of these suggestions will work (or if they are available in your database version).
Basically you need to get the result of the query with the Group by first and then run the query to compare the Summed values. This can be accomplished with either a Common Table Expression or a Nested/Derived table.
Common Table Expression example:
Code:
WITH CTE_RESULT AS
(SELECT STOCK.CODE
, STOCK.DESCR
, STOCK.SUPPLIER
, STOCK.STK
, SUM(SORDERS.QTY) AS SUM_QTY
, STOCK.ORDERED
, STOCK.MINSTOCK
, STOCK.MAXSTOCK
FROM STOCK
INNER JOIN
SORDERS
ON STOCK.CODE = SORDERS.CODE
GROUP BY STOCK.CODE
)
SELECT column-list
FROM CTE_RESULT
WHERE STK + ORDERED < SUM_QTY
Nested/Derived table example:
Code:
SELECT column-list
FROM (SELECT STOCK.CODE
, STOCK.DESCR
, STOCK.SUPPLIER
, STOCK.STK
, SUM(SORDERS.QTY) AS SUM_QTY
, STOCK.ORDERED
, STOCK.MINSTOCK
, STOCK.MAXSTOCK
FROM STOCK
INNER JOIN
SORDERS
ON STOCK.CODE = SORDERS.CODE
GROUP BY STOCK.CODE
) AS A
WHERE STK + ORDERED < SUM_QTY
dBforums
>
Inner Join
01-19-10, 03:53
Linear Mode
