This would be done with a stored procedure. The first step is to create a table of the item number(s) to be updated. This is done with a query that would return a table of distinct entries for the one item or all items. This is accomplished in the first SELECT in the code. The tests for the item number and location code are performed in the two ‘AND’ portions of the WHERE clause. If you look at that code snippet, you will notice that the item number and location code both have to match the input parameters in order to be returned. This will return exactly one row (thanks to the SELECT DISTINCT). If you pass in ‘ALL’ for the item number and location code, none of the rows will match, but the input parameters are ‘ALL’, so both conditions will return TRUE, thus selecting all items.
Next, a cursor is used to iterate over the result set of the first query. Since I will be updating the sum of the quantities, I need to create a derived table to hold the sum. This is done in the JOIN statement. The item number and location code are included so that everything I need is on that one table.
While testing my queries that could change the contents of a table, I like to list the rows that would be affected. This lets me look at what the query is doing before that changes are made to table(s). Once I am satisfied with the results, I change the SELECT to an UPDATE. I keep the SELECT in the query (commented out) in case I need to go back and revisit it some time in the future.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dd_IV00102_REALLOCATE'
AND type = 'P')
DROP PROCEDURE dd_IV00102_REALLOCATE
GO
CREATE PROCEDURE dd_IV00102_REALLOCATE
-- dd_IV00102_REALLOCATE '164254', 'WAREHOUSE1'
-- dd_IV00102_REALLOCATE 'ALL', 'ALL'
@itemNumb AS VARCHAR(31),
@locncode AS VARCHAR(11)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE SOPCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT L.ITEMNMBR, L.LOCNCODE
FROM SOP10200 L
WHERE L.SOPTYPE = 2
AND (L.ITEMNMBR = @itemNumb OR @itemNumb = 'ALL')
AND (L.LOCNCODE = @locncode OR @locncode = 'ALL')
OPEN SOPCursor
WHILE 1=1
BEGIN
FETCH NEXT FROM SOPCursor INTO @itemNumb, @locncode
if @@fetch_status <> 0 begin
break
end
UPDATE dbo.IV00102 SET ATYALLOC = N.QTY
-- SELECT I.ATYALLOC, N.*
FROM dbo.IV00102 I
JOIN (
SELECT L.ITEMNMBR, L.LOCNCODE, SUM(L.QUANTITY) AS QTY
FROM dbo.SOP10200 L
WHERE L.ITEMNMBR = @itemNumb AND L.LOCNCODE = @locncode AND L.SOPTYPE = '2'
GROUP BY L.ITEMNMBR, L.LOCNCODE
) N ON I.ITEMNMBR = N.ITEMNMBR AND I.LOCNCODE = N.LOCNCODE
END
CLOSE SOPCursor
DEALLOCATE SOPCursor
GO
grant exec on dd_IV00102_REALLOCATE to public