Cursor update error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor update error

Author

Topic

timmoser
Starting Member

8 Posts

Posted - 2004-07-20 : 10:29:02

I have a SP that contains a cursor with an inner cursor.
The SP works mostly, but in the inner cursor I get an
error after it updates the first record. Below is the info:

Error:

"Error:  Number (560) Severity (16) State (1) Procedure (_test) 
The UPDATE/DELETE WHERE CURRENT OF failed for the cursor 'my_curse_bos' 
because it is not positioned on a row."


DECLARE my_curse_def CURSOR FOR (SELECT part, qty FROM #availdef )
DECLARE @reccount INT
OPEN my_curse_def
FETCH my_curse_def into @part, @def_qty

WHILE (@@sqlstatus = 0)
BEGIN
SELECT @varqty=@def_qty
SELECT @reccount=0
SELECT @reccount = (SELECT COUNT(*) FROM #bosiwant WHERE part=@part)

DECLARE my_curse_bos CURSOR FOR (SELECT part, qty_ord, allocate, asl_ord, t_num, line FROM #bosiwant WHERE part=@part) FOR UPDATE OF allocate 
OPEN my_curse_bos  
FETCH my_curse_bos into @curPart, @qty_ord, @allocate, @asl_ord, @t_num, @line   
    
    WHILE (@reccount>0)
    BEGIN
   
        IF  @varqty>0
        BEGIN
             IF @qty_ord>=@varqty  
             BEGIN
                update #bosiwant
                set allocate = @varqty
                where current of my_curse_bos
                SELECT @varqty=0
             END
	ELSE
             BEGIN
                update #bosiwant
                set allocate = @qty_ord
                where current of my_curse_bos
                SELECT @varqty=@varqty-@qty_ord
             END   
        END
   
        FETCH my_curse_bos into @curPart, @qty_ord, @allocate, @asl_ord, @t_num, @line

	SELECT @reccount=@reccount-1

    END

    CLOSE my_curse_bos
    deallocate cursor my_curse_bos        

FETCH my_curse_def into @part, @def_qty

END

close my_curse_def
deallocate cursor my_curse_def

It actually does update the first record in the inner
the cursor; seems like after the update the cursor is no more.

Thanks for your thoughs.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-20 : 12:30:39

Could you explain what your code is doing? Why do you need a cursor? Why do you need two cursors? Typically you can get rid of cursors by using UPDATE/INSERT/DELETE statements with JOINs.

Tara

timmoser
Starting Member

8 Posts

Posted - 2004-07-21 : 13:44:00

I would love NOT to use a cursor but I don't see any other way around it. I have two tables like so...

table_of_bos
ponum part boqty Allocate
A1 120A 10 --
Z5 120A 3 --
A1 490C 5 --
B3 490C 3 --
C6 490C 6 --

table_of_inv
part qty
120A 18
490C 10
560F 90

I need to fill the allocate qty in the "table_of_bos" table up to the boqty until I run out of "qty" in the "table_of_inv" table. So the result would look something like this...

table_of_bos
ponum part boqty Allocate
A1 120A 10 10
Z5 120A 3 3
A1 490C 5 5
B3 490C 3 3
C6 490C 6 2

I cannot exceed the qty available in the inventory table. Any idea on how to fix what I've done so far or a better way to do it would be greatly appreciated.

Thanks

timmoser
Starting Member

8 Posts

Posted - 2004-07-21 : 16:07:32

Just what to make sure I answered all of your questions.

"Could you explain what your code is doing?"
>>> The outer cursor loops over a unique list of parts with a qty for each part. For each part in the list the inner cursor (a list of backorders) will allocate the qty to fill back orders until it runs out of parts in inventory.

Why do you need a cursor? Why do you need two cursors?
>>> I don't know a better way. (Wish I did)

Typically you can get rid of cursors by using UPDATE/INSERT/DELETE statements with JOINs.
>>> Most of the time I have been able to do just that.

Thanks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-21 : 16:34:48

I have not had time to look at this. It would be very helpful if you provided the tables in CREATE TABLE format, the sample data with INSERT INTO statements. Read through this for more information (make sure to look at the link provided in the initial post):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

With this information, we can then run this on our machines to give you a good answer. Without this, we end having to type out the information so that makes it less likely for us to answer.

Tara

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-21 : 17:28:59

No time to look at this right now. Here is some DDL:

create table table_of_bos (ponum Char(2), part char(4), boqty int, allocate int)
insert table_of_bos select 'A1', '120A', 10, 0
insert table_of_bos select 'Z5', '120A', 3, 0
insert table_of_bos select 'A1', '490C', 5, 0
insert table_of_bos select 'B3', '490C', 3, 0
insert table_of_bos select 'C6', '490C', 6, 0

create table table_of_inv (part char(4), qty int)

insert table_of_inv select '120A', 18
insert table_of_inv select '490C', 10
insert table_of_inv select '560F', 90

--Ken
Your Kung-Fu is not strong. -- 'The Core'

timmoser
Starting Member

8 Posts

Posted - 2004-07-22 : 08:54:00

Sorry and thanks "tduggan" I didn't relize that was what you were looking for.
Thanks "kselvia" looks like you did my homework.

timmoser
Starting Member

8 Posts

Posted - 2004-07-23 : 09:02:11

Anyone have any idea? I am really stuck on this one. Any help with the cursor or a better idea would be appreciated. Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-23 : 09:13:58

does either table have a primary key? To avoid using cursors, those will always be required. Also, if you can add a litte more sample data that would be nice.

I do suspect, however, that a loop of some sort might be required unfortunately. But hopefully we can do a "set-based" loop instead of a cursor.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 09:28:48

Can you just use an UPDATE on the inner loop? (it will run MUCH faster than a cursor, but I can;t see a way to STOP it when it has allocated all that is available - i.e. it will go on changing the amounts to 0 on all remaining lines)
Something like:


DECLARE @allocated int	-- Working storage
UPDATE	#bosiwant
SET
	-- Remember amount allocate to THIS item
	@allocated =
		-- Update [allocate] column
		allocate = 
			CASE WHEN qty_ord >= @varqty 
				THEN @varqty	-- Not enough
				ELSE qty_ord	-- Plenty
				END,
	-- Adjust remaining QTY by the amount allocate to THIS item
	@varqty = @varqty - @allocated
FROM	#bosiwant 
WHERE	part = @part

I notice there is no ORDER BY on your inner cursor - so you are allocated on a "random basis" rather than "oldest first" or somesuch - is that right?

Also, looks like it needs to take care of rows where the [allocate] already contains a part-allocation, but maybe #bosiwant has already worked out the remaining-quantity-wanted before we get to this point.

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-23 : 16:04:05

How about this (I added an entry to the test table to test that it stops allocating when no inventory is available):


SET NOCOUNT ON

create table #table_of_bos (ponum Char(2), part char(4), boqty int, allocate int)
insert #table_of_bos select 'A1', '120A', 10, 0
insert #table_of_bos select 'Z5', '120A', 3, 0
insert #table_of_bos select 'A1', '490C', 5, 0
insert #table_of_bos select 'B3', '490C', 3, 0
insert #table_of_bos select 'C6', '490C', 6, 0
insert #table_of_bos select 'C9', '490C', 6, 0

create table #table_of_inv (part char(4), qty int)

insert #table_of_inv  select '120A', 18
insert #table_of_inv  select '490C', 10
insert #table_of_inv  select '560F', 90

DECLARE @available INT, @part char(4), @allocate int, @remain int
SET @part = ''

SELECT id = IDENTITY(INT,1,1), y.ponum, x.part, boqty,
	CASE WHEN ipo = y.ponum 
             THEN x.qty ELSE 0 END allocate
INTO #bosiwant
FROM (
	SELECT (SELECT TOP 1 ponum 
		FROM #table_of_bos 
		WHERE part = o.part 
		ORDER BY ponum
		) ipo,
		part, qty
	FROM #table_of_inv o
) x,
#table_of_bos y
WHERE y.part = x.part 
ORDER BY x.part, y.ponum

UPDATE #bosiwant
SET 
@available = 
          CASE WHEN @part = part
               THEN @available - @remain 
               ELSE allocate
           END,

@allocate = allocate =
          CASE WHEN boqty <= @available 
	       THEN boqty
               ELSE @remain
           END,

@remain = 
          CASE WHEN @available - boqty > 0 
               THEN @available - @allocate 
               ELSE 0 
          END,

@part = part

UPDATE #table_of_bos 
SET allocate = #bosiwant.allocate
FROM #bosiwant
WHERE #table_of_bos.ponum = #bosiwant.ponum
AND   #table_of_bos.part = #bosiwant.part

SELECT * 
FROM #table_of_bos 

DROP TABLE #bosiwant
DROP TABLE #table_of_bos 
DROP TABLE #table_of_inv

-----------------------------------

ponum part boqty       allocate    
----- ---- ----------- ----------- 
A1    120A 10          10
Z5    120A 3           3
A1    490C 5           5
B3    490C 3           3
C6    490C 6           2
C9    490C 6           0

You don't say how you are going to upate table_of_inv with remaining values. That would be easy to do here if you wanted to...

--Ken
Your Kung-Fu is not strong. -- 'The Core'

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-23 : 21:09:02

I realized you don't really need the complicated query and temp table. This will correctly update the table_of_bos table.


DECLARE @available INT, @part char(4), @allocate int, @remain int
SET @part = ''

UPDATE table_of_bos
SET 
@available = 
          CASE WHEN @part = table_of_bos.part
               THEN @available - @remain 
               ELSE qty
           END,

@allocate = allocate =
          CASE WHEN boqty <= @available 
	       THEN boqty
               ELSE @remain
           END,

@remain = 
          CASE WHEN @available - boqty > 0 
               THEN @available - @allocate 
               ELSE 0 
          END,

@part = table_of_bos.part
FROM table_of_inv
WHERE table_of_inv.Part = table_of_bos.Part

SELECT * 
FROM table_of_bos

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources