Why my cursor isn't returning the first row values?-CodePudding

I have a cursor in order to separate rows based on a required quantity for each item, this works kind of fine, with the exception that it doesn't return the first rows and instead, if the row for example has a value of quantity 3, it adds it to the last row. My code is the following:

DECLARE curCURSOR CURSOR 
FOR 
SELECT ITEM, CITY, OPEN_QUANTITY, MODEL, PROMISE , SALES_ORDER , LINE_NUMBER, DESCRIPTION, CARRIER FROM T_tmpShipReporCommAssy
WHERE [DESCRIPTION]  like 'COM%ASSY%' AND CITY = @pSite 
AND FG_AVAILABLE > 0
ORDER BY  PROMISE, SALES_ORDER, LINE_NUMBER

OPEN curCURSOR
FETCH curCURSOR INTO @ITEM, @CITY, @required_quantity, @MODEL, @PROMISE, @SALES_ORDER, @LINE_NUMBER, @DESCRIPTION, @CARRIER
    IF EXISTS(SELECT 1 FROM AvailToShipPalletsSN)
    BEGIN
        DELETE FROM AvailToShipPalletsSN
        DBCC CHECKIDENT ('[AvailToShipPalletsSN]', RESEED, 0);
    END
DECLARE @e INT
SET @e = 0

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH FROM curCURSOR INTO @ITEM, @CITY, @required_quantity, @MODEL, @PROMISE, @SALES_ORDER, @LINE_NUMBER, @DESCRIPTION, @CARRIER
    WHILE (@e < @required_quantity)
    BEGIN
        INSERT INTO AvailToShipPalletsSN (item, city, required_quantity, MODEL, PROMISE, SALES_ORDER, LINE_NUMBER, DESCRIPTION, CARRIER) values
            (@ITEM, @CITY, @required_quantity, @MODEL, @PROMISE, @SALES_ORDER, @LINE_NUMBER, @DESCRIPTION, @CARRIER)
        SET @e = @e   1
    END
    SET @e = 0
END

CLOSE curCURSOR
DEALLOCATE curCURSOR

Now, when I run a select count to the original table and the generated one:

       SELECT MODEL, SUM(OPEN_QUANTITY) FROM T_tmpShipReporCommAssy
       WHERE [DESCRIPTION]  like 'COM%ASSY%' AND CITY = @psite
       AND FG_AVAILABLE > 0
       AND PROMISE <= @pdate
       GROUP BY MODEL

       SELECT model, COUNT(required_quantity) FROM AvailToShipPalletsSN 
       group by model

It returns me the following results:

(Table with correct quantities)

CMF200M 10

CMF010M 2

CMF200A 1

H200F 2

(Table generated by my cursor)

CMF200M 8

CMF010M 2

CMF200A 1

H200F 4

I don't really know how to fix this behavior, I'd appreciate a little help on this topic

CodePudding user response：

Cursors are very slow and inefficient, they can also be cumbersome to code against.

You can completely obviate the need for this slow and incorrect cursor, by using an inline tally table.

This is all the code you need:

IF EXISTS(SELECT 1 FROM AvailToShipPalletsSN)
BEGIN
    TRUNCATE AvailToShipPalletsSN;
    DBCC CHECKIDENT ('[AvailToShipPalletsSN]', RESEED, 0);
END;

WITH
    L0 AS ( SELECT 1 AS c 
            FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
                        (1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
    L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
    L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B )
-- add more cross-joins if you need more than 65536 rows
INSERT INTO AvailToShipPalletsSN
    (item, city, required_quantity, MODEL, PROMISE, SALES_ORDER, LINE_NUMBER, DESCRIPTION, CARRIER)

SELECT
  ITEM, CITY, OPEN_QUANTITY, MODEL, PROMISE, SALES_ORDER, LINE_NUMBER, DESCRIPTION, CARRIER
FROM T_tmpShipReporCommAssy t
CROSS APPLY (
    SELECT TOP (t.OPEN_QUANTITY) *
    FROM L2
) nums
WHERE [DESCRIPTION]  like 'COM%ASSY%' AND CITY = @pSite 
  AND FG_AVAILABLE > 0;

Essentially, what this does is, for every row in the base table, apply a bunch of rows to it. The number of rows added is equal to OPEN_QUANTITY, and the rows are constructed using cross-joined VALUES clauses.

CodePudding user response：

The problem is that you fetch a row just after you open the cursor, but don't do anything with the fetched values. So you end up missing the first row. To correct that, reorganize your code this way:

-- first delete the other table
if exists(select ....)
begin
  delete ...
end

-- then, declare variables, open the cursor, etc.
declare curCURSOR cursor for ...
declare @e int = 0

open curCURSOR

-- then start fetching. use the following pattern:
fetch from curCURSOR into @...
while @@FETCH_STATUS = 0
begin
  -- do your thing:
  while @e < @required_quantity
    ...
  -- then, after you did your thing
  -- and just before the end of the while @@fetch_status block,
  -- fetch again
  fetch from curCURSOR into @...
end

-- finally close, deallocate, etc.
close ...
deallocate ...

That takes care of missing the first row.

The reason of your second problem might be that you added a condition to the query that isn't in the cursor: PROMISE <= @pdate.

That is, there might be some rows with promise after @pdate that got inserted into the second table, but don't show when you query the first table with that condition.