The following script below was written in SQL 2000 and worked fine. Now that we have moved to SQL 2008 it errors out with;
(1 row(s) affected)
Msg 16947, Level 16, State 1, Line 21
No rows were updated or deleted.
The statement has been terminated.
Line 21 is:UPDATE LarryAlkon
LarryAlkon is a view.
DECLARE @req_ship_dt varchar(20), @qty_to_ship decimal(13,4), @qty_bkord decimal(13,4),
@qty_ordered decimal(13,4), @qty_on_hand decimal(13,4), @qty_allocated decimal(13,4), @so_qty_bkord decimal(13,4)
DECLARE abc CURSOR FOR
SELECT req_ship_dt, qty_to_ship, qty_bkord, so_qty_bkord, qty_ordered,
qty_on_hand, qty_allocated FROM LarryAlkon
ORDER BY req_ship_dt
for update of qty_to_ship, so_qty_bkord, qty_bkord;
OPEN abc
FETCH NEXT FROM abc INTO @req_ship_dt, @qty_to_ship, @qty_bkord,
@so_qty_bkord, @qty_ordered, @qty_on_hand, @qty_allocated
WHILE (@@FETCH_STATUS = 0)
BEGIN--While
DECLARE @available decimal(13,4)
SET @available = @qty_on_hand + @qty_bkord - @qty_allocated
IF (@available > 0) BEGIN --1
IF (@available > @so_qty_bkord) BEGIN --2
UPDATE LarryAlkon
SET qty_to_ship =(SELECT qty_ordered FROM oeordlin_sql WHERE ord_type=LarryAlkon.ord_type
AND ord_no=LarryAlkon.ord_no AND line_seq_no=LarryAlkon.line_seq_no), so_qty_bkord = 0
WHERE CURRENT OF abc
UPDATE LarryAlkon
SET qty_bkord = @qty_bkord-@so_qty_bkord
WHERE CURRENT OF abc
END --2
ELSE BEGIN --3
UPDATE LarryAlkon
SET qty_to_ship = @qty_to_ship + @available, so_qty_bkord = @so_qty_bkord - @available
WHERE CURRENT OF abc
UPDATE LarryAlkon
SET qty_bkord = @qty_bkord - @available
WHERE CURRENT OF abc
END --3
END --1
FETCH NEXT FROM abc INTO @req_ship_dt, @qty_to_ship,
@qty_bkord, @so_qty_bkord, @qty_ordered, @qty_on_hand, @qty_allocated
END --While
CLOSE abc
DEALLOCATE abc
GO
--ADDED BY PETE TO CLEAR BO QTY FLAG WHEN FILLED
UPDATE oeordlin_sql
SET qty_bkord = 0
WHERE (qty_to_ship - qty_ordered = 0) AND (qty_bkord <> 0)
GO
--ADDED BY PETE TO CALCULATE NEW BO QTY WHEN FILLED
UPDATE oeordlin_sql
SET qty_bkord = qty_ordered - qty_to_shiP
WHERE (ord_type = 'O') AND (qty_ordered - qty_to_ship <> qty_bkord) AND (bkord_fg = 'Y')
GO