November 24, 2023 at 6:24 pm
I want to update the "QTY_REQ" column in the PLSQL table (in the screen cap attached) sequentially row by row by dividing out a number (e.g. 7) among the rows. I want to start with the row with the highest value in QTY (QTY 4, LOC 10800B41) and enter 4 for QTY_REQ. Then go to the second row (QTY 2, LOC 10800A01) and enter 2 for QTY_REQ. Then finally go to the third row (QTY 2, LOC 10800B01) and enter 1 for QTY_REQ. So I evenly divide out the values and stop when the total is reached. The number could be 5 and would just fill in the first two rows (4 entered for for QTY_REQ for the first and 1 entered for for QTY_REQ for the second).
Important point; the value that is entered for QTY_REQ cannot be higher than the value that is in the QTY column for that row.
If someone can help with this that would be appreciated, thank you.
November 24, 2023 at 6:57 pm
this isn't a Oracle site - you better off asking elsewhere
November 24, 2023 at 7:20 pm
OK sorry about that (and for the duplicate post)
November 24, 2023 at 9:47 pm
I do agree that this is better posted on an "Oracle" forum, but you are going to hit snags still by asking there. The big question that will come up is what defines the row order? What I mean is why would you pick 2 from location 10800A01 and 1 from location 10800B01? Why not the other way around or even from other locations? That being said, you MAY have a sorting on the table already in which case the above point can be ignored. BUT if you don't have any sorting on the table, I'd add a column that you can use to sort things. Looking at the table, I suspect it is for something along the lines of a warehouse system in which case a datetime column is likely a good choice as that way you can look at FIFO or LIFO for your data.
But ignoring that, the logic will be similar no matter what database tool you use (SQL Server, PLSQL, MySQL, etc); just the syntax for it will be different. The way I'd do it is to get a rolling sum on the quantity and insert all of that data into a temp table while the rolling sum is less than or equal to the quantity required plus 1 extra row. So in your example, if you were looking for 7, that rolling sum would be 4 then 6 and the next row is larger than the input, but we need that row, so we grab it too and we have 4, 6, and 8. In SQL Server, I'd do this by doing the check on the input quantity being > LAG(quantity,1). Functions in WHERE clauses are bad for performance, but they are not the worst thing in the world and sometimes are a necessary evil. There are alternate ways to do it such as doing a row count where the rolling sum is less than the input value then grabbing the top N+1 rows. I'm not trying to limit your approach; just saying how I'd do it.
Next, we update the qty required value in the temporary table to match the quantity as long as the rolling sum is less than the quantity input. Last step, we update the quantity required of the last row (where input <= rolling sum) and then do some math on it - input quantity - (rolling sum - quantity at location). In your example, that calculation would be 7-(8-2) or 7-(6) or 1. Now if the 3rd location had quantity 3, the numbers would be 7-(9-3) or 7-(6) or 1. You could bake the update into a single statement by using a CASE statement for when input quantity is > quantity at the location, then update to quantity else do the math, and that's how I'd do a tiny optimization to the query, but my first run at it would be to break it up to multiple small steps and validate each one works how I want. Once I have all the small steps figured out, then look at which steps can be combined or skipped over and optimize to the "good enough" point. Good enough MAY be making it 100% optimized, or it may be your first run at it runs "good enough". I know I have some slow queries on my system (run in 2+ seconds), but they are deemed "good enough" because of how often they are used or by when they are run (after hours vs in meetings with the CEO).
Now that you have the values you want to update things to, it's a simple update command from your temp table to the real table.
Now, the above is a 4 step process - create temp table, update non-max quantity, update max quantity, update main table. So it may not be the most efficient approach for large data sets, but should give you the results you want. You can skip the "update max quantity" step, which saves you one step against the temp table. Nice thing about having it all in the temp table is you can validate the data before writing back to the main table.
I'm sure that others will say that this doesn't need a temp table and it doesn't. It would be faster without, and you could do it with a nested select and similar logic as above. I just like the above approach to build the initial query so you can get the results you want and then tune the query later if needed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 25, 2023 at 3:14 am
OK sorry about that (and for the duplicate post)
Heh... I can just imagine what's going through your mind... "But I posted it on the [Working in Oracle] forum".
One of the better sites I've seen in the distant past was the "Ask Tom" site and that "Tom" is none other than Tom Kyte himself. It's still around at the following URL. I can make no current claims for it or against it because I haven't visited it since 2008 or so but it was awesome back then. I also don't believe that someone like Tom Kyte would have let something with his name on it falter.
https://asktom.oracle.com/pls/apex/f?p=100:23::ABOUT::::
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2023 at 8:25 am
Thanks for the replies. I found a solution. I will post it here when I find time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply