Jim:
I'm not sure I understand your needs but it seems you want to display
availability of each part for an order based on some type of date. I did this
in a previous life on a different platform but the logic is the same. I really
don't think you need a nested cursor if you're doing this for one item,
however, I'm sending some logic that with modifications I think will help. The
C2 cursor is where the real action occurs. I think it could be modified to fit
your needs if I understand correctly.
Bill Milyard
CLEAR ALL VAR
SET VAR vOnHandInv INT
SET VAR vOrderNum TEXT
SET VAR vProduct TEXT
SET VAR vQtyNeeded INT
SET VAR vRemainingNeeds INT
SET VAR vOnHandInv INT
SET VAR vPromisedDate DATE
SET VAR vQtyConsumed INT
DROP CURSOR c1
DROP CURSOR c2
-- Read through an order for each part using cursor C1
DECLARE c1 CURSOR FOR SELECT OrderNum,Product,QtyNeeded FROM MyOrders
Open C1
FETCH C1 INTO vOrderNum,vProduct,vQtyNeeded
WHILE SQLCODE <> 100 THEN
-- Then read through the PARTS file for each item
DECLARE c2 CURSOR FOR SELECT PromisedDate,OnHandInv FROM MyParts +
WHERE Product = .vProduct ORDER BY PromisedDate
OPEN c2
FETCH C2 INTO vPromisedDate,vOnHandInv
SET VAR vRemainingNeeds = (.vQtyNeeded)
WHILE SQLCODE <> 100 AND .vRemainingNeeds > 0 THEN
IF vRemainingNeeds >= vOnHandInv THEN
SET VAR vRemainingNeeds = (.vRemainingNeeds - .vOnHandInv)
SET VAR vQtyConsumed = (vOnHandInv)
SET VAR vOnHandInv = 0
ELSE
SET VAR vOnHandInv = (.vOnHandInv - .vRemainingNeeds)
SET VAR vQtyConsumed = (.vRemainingNeeds)
SET VAR vRemainingNeeds = 0
ENDIF
UPDATE MyParts SET OnHandInv = (.vOnHandInv), InvUsed = (.vQtyConsumed), +
RemainNeeds = (.vRemainingNeeds) WHERE CURRENT OF c2
FETCH C2 INTO vPromisedDate,vOnHandInv
ENDWHILE
DROP CURSOR c2
FETCH C1 INTO vOrderNum, vProduct, vQtyNeeded from MyOrders
SET VAR vRemainingNeeds = (.vQtyNeeded)
ENDWHILE
DROP CURSOR C1
________________________________
From: 'Jim Belisle' via RBASE-L <[email protected]>
Sent: Wednesday, December 16, 2020 1:40 PM
To: [email protected] <[email protected]>
Subject: RE: [RBASE-L] - proper code
Karen,
Thanks for the code. I tried this below and here is where the problem is.
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
DROP CURSOR c2
SET ERROR MESSAGE 705 ON
DECLARE c1 CURSOR FOR SELECT partnum +
FROM topnopuinfo WHERE dateprom IS NOT NULL +
ORDER BY partnum, dateprom, control#, model#
OPEN c1
FETCH c1 INTO vpnum ivpnum
DECLARE c2 CURSOR FOR SELECT control#, model#, needpcs +
FROM topnopuinfo +
WHERE dateprom IS NOT NULL AND partnum = .vpnum +
ORDER BY dateprom, control#, model#
WHILE SQLCODE <> 100 THEN
SELECT totinvpcs INTO vinvamt FROM inv_all_open WHERE partnum=.vpnum
OPEN c2 RESET
FETCH c2 INTO vcnum ivcnum, vmnum ivmnum, vnpcs ivnpcs
WHILE SQLCODE <> 100 THEN
SELECT SUM(invused) INTO vsumused FROM topnopuinfo WHERE partnum=.vpnum
SET VAR vinvleft = (.vinvamt-(.vsumused+.vnpcs))
SET VAR vinvuse = .vnpcs
IF vinvleft < 0 THEN
SET VAR vinvleft = 0
SET VAR vinvuse = 0
ENDIF
UPDATE topnopuinfo SET invleft=.vinvleft,invused=.vinvuse +
WHERE partnum=.vpnum AND control#=.vcnum AND model#=.vmnum
FETCH c2 INTO vcnum ivcnum, vmnum ivmnum, vnpcs ivnpcs
ENDWHILE
FETCH c1 INTO vpnum ivpnum
ENDWHILE
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
DROP CURSOR c2
SET ERROR MESSAGE 705 ON
RETURN
As you can see in the below table, the first part, 21IBL-235S was updated (we
had no inventory) and went onto the next part, 2BG-1.
So the first cursor successfully went to the next part.
The second cursor went through the two different orders (control# col) for the
two different models showing the second cursor was successful.
Invused and invleft were properly updated.
[cid:[email protected]]
But once 2BG-1 part was completed with these two orders, the cursor went right
back to the second line, shown below, instead of going onto 2BG-3L on the
fourth line.
[cid:[email protected]]
If you see something I am doing wrong, I would appreciate any suggestions.
James Belisle
Making Information Systems People Friendly Since 1990
[cid:[email protected]]
From: 'Karen Tellef' via RBASE-L <[email protected]>
Sent: Tuesday, December 15, 2020 10:19 AM
To: [email protected]
Subject: Re: [RBASE-L] - proper code
CAUTION:This is an EXTERNAL EMAIL, STOP! and think before clicking on any links
or opening attachments.
Don't have any brilliant ideas for you. But if you need an example of a nested
cursor, here's what I usually do. Often the 2nd cursor will have a where
clause using variables from the 1st cursor, so you need to initialize those
variables before the Declares
DROP CURSOR c1
DROP CURSOR c2
DECLARE c1 CURSOR FOR SELECT columns FROM table WHERE ...
DECLARE c2 CURSOR FOR SELECT columns FROM table WHERE ...
OPEN c1
WHILE 1 = 1 THEN
FETCH c1 INTO vars
IF SQLCODE = 100 THEN
BREAK
ENDIF
*( do stuff with 1st table)
OPEN c2 RESET
WHILE 1 = 1 THEN
FETCH c2 INTO vars
IF SQLCODE = 100 THEN
BREAK
ENDIF
*(do stuff with 2nd table)
ENDWHILE
ENDWHILE
DROP CURSOR c1
DROP CURSOR C2
Karen
-----Original Message-----
From: 'Jim Belisle' via RBASE-L
<[email protected]<mailto:[email protected]>>
To: [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>>
Sent: Mon, Dec 14, 2020 4:13 pm
Subject: [RBASE-L] - proper code
Background: We ship out products that are unassembled. That means what we ship
out are individual parts and assemblies we make.
Problem: We want to look at a form I am creating so that any part looked at for
any product on an open order, the user will be able to see if our existing
inventory has that part covered. This is based upon the date we plan we ship it.
Goal: I am trying to update usable inventory based upon part numbers used in a
product and promised ship dates.
So the code I need is code that will look at the table where this info is
located, find each part, find the promised date (ascending), find the product
the part belongs to, then update the needed amt to 0 (for scheduling the hours
needed) if we have the parts available. The actual inventory amount will not
change, but the variable used during the process will update to take the actual
inv amt, subtract the amt needed to fill the next promised date for that part
belonging to the product.
So we want to be able to look at the form in this manner.
Part product promised qty need
need to make start inv amt inv left var
Part A 8BLA15 01/05/2021 15
0 100 85
Part A 8BLA16 01/05/2021 15
0 70
Part A 8BLA17 01/08/2021 75
5 0
I was looking at using CURSORS to do the job but not sure if that is the most
efficient way. Nor was I sure of how to set up a multiple cursor code.
I think I would need a nested or maybe a double nested cursor.
Any thoughts?
James Belisle
Making Information Systems People Friendly Since 1990
[cid:[email protected]]
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
[email protected]<mailto:[email protected]>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/CY4PR0201MB339480EDA63A37780DC9E045A0C70%40CY4PR0201MB3394.namprd02.prod.outlook.com<https://groups.google.com/d/msgid/rbase-l/CY4PR0201MB339480EDA63A37780DC9E045A0C70%40CY4PR0201MB3394.namprd02.prod.outlook.com?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
[email protected]<mailto:[email protected]>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/424602862.749249.1608049147394%40mail.yahoo.com<https://groups.google.com/d/msgid/rbase-l/424602862.749249.1608049147394%40mail.yahoo.com?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
[email protected]<mailto:[email protected]>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/CY4PR0201MB3394B2E94B1AB8464328B812A0C50%40CY4PR0201MB3394.namprd02.prod.outlook.com<https://groups.google.com/d/msgid/rbase-l/CY4PR0201MB3394B2E94B1AB8464328B812A0C50%40CY4PR0201MB3394.namprd02.prod.outlook.com?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/MN2PR10MB40468B325F59DA7AC022CCF3CBC40%40MN2PR10MB4046.namprd10.prod.outlook.com.
