Bill, Thanks for the advice. I will save this for possible use in the future. You were correct in your observation that I would not need a nested cursor. I figured it out last night after walking away and just thinking. I should have said something on the list. The single cursor worked perfectly.
James Belisle Making Information Systems People Friendly Since 1990 [cid:[email protected]] From: [email protected] <[email protected]> On Behalf Of BILL MILYARD Sent: Thursday, December 17, 2020 2:55 PM To: [email protected] Subject: Re: [RBASE-L] - proper code 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]<mailto:[email protected]>> Sent: Wednesday, December 16, 2020 1:40 PM To: [email protected]<mailto:[email protected]> <[email protected]<mailto:[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]<mailto:[email protected]>> Sent: Tuesday, December 15, 2020 10:19 AM To: [email protected]<mailto:[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/CY4PR0201MB3394D0D43199C2B22A4152A5A0C40%40CY4PR0201MB3394.namprd02.prod.outlook.com.
