In this case, I have an Invoice Master File where each Invoice has about 5 rows. There are maybe 100 invoices in the file and just the first row of each invoice is shown on screen.
The operator can click on several invoices to mark them for deletion.
I first send the master file to the server with the check marks. I have developed the stored procedure below using a 3rd party tool.
DELIMITER $$
CREATE FUNCTION DeleteChkInv ( )
RETURNS int;
SET xinv1 = 0;
WHILE xinv1 = 0
BEGIN
SELECT @minv := Invnbr FROM Invoices02 WHERE Check01 = 1; @v1 := SELECT FOUND_ROWS();
if @v1 > 0
DELETE FROM Invoices02 WHERE Invnbr = @minv;
else
xinv1 = 1
endif
END; $$
DELIMITER ;
I am trying to create a WHILE Loop that looks for the first Check Mark (Check01 = 1). As it finds each Checkmark, it deletes all the Invoice Rows with the Invoice Number on the Check Mark Row.
It continues with this until it can find no more checkmarks and sets xinv = 1 to end the loop.
This has so many error in it that I can make no sense of the debugger.
Maybe if you could find 1 error, I can start with that and see if the debugger will become more helpful.
No I don't have it working outside of ReTool.
I have been writing the code in cPanel under GoDaddy. It has an editor that looks for syntax problems and the code has been modified to show no errors in the code itself but when I try to run it, it gives the generic answer about checking the manual.
I have also submitted this to the Oracle MySQL Community. They may come up with something.
I think I have this solved and if you can believe it I got it from ChatGPT.
I have not fully tested the code but a preliminary test shows that it may work.
I want to fully test this before presenting it back.
Its code like I have never seen before. I will present this back once I have fully checked this out.
Mike
I have been a long time getting back on this because of other pressures.
I want to repeat the original problem because it is a long ways back.
I have an Invoice Master File where each Invoice has about 5 rows. There are maybe 100 invoices in the file and just the first row of each invoice is shown on screen. See the attached screen capture.
The idea is we want the operator to click on multiple rows and then click "Delete Selected Invoices" and have all rows of each checked invoice deleted.
I thought I would need a Stored Procedure but its not required,
I got the answer from ChatGPT as below:
DELETE FROM Invoices02
WHERE Invnbr IN (
SELECT Invnbr FROM (
SELECT Invnbr FROM Invoices02
WHERE Check01 = 1
GROUP BY Invnbr
) AS subquery
);
Its not code like I have seen before. The Oracle Web Site was stumped by this.
But it works. You have to return the screen table to the server before running this.
