×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
I've had a few times where I need to predict the onhand as product is being consumed or shipped. I use an OLAP sum() type function to subtotal the demand compared to the onhand to predict what the projects onhand will be at each record as demonstrated below. However, I'm seeing a new level of requirement, which is to make a determination on a records value and remove it from the list and then recalculate the projected onhand again without that record being there to see if having that requirement removed leaves onhand for other records.
My current method is to close and re-open the cursor every time I remove one of these records from the list, but I have to do it hundreds of times, so is there a way to have the SQL engine iterate or calculate this better?
Select ORD, LINE, PART, ShipDate,
Sum(ORDFILE.QTYORD) over partition by PART order by ShipDate,ORD,LINE rows between unbound preceding and current row) as AccumDemand
From ORDFILE
Cross join lateral
(values(select sum(OnHand) from INVFILE
Where IPROD=ORD.PART) as OnHand
Cross join lateral
(values(select 'C' from NoteFile
Where Note like '%SHIP COMPLETE%'
Fetch first row only) L2(ShipComplete)
Where ORD not in (select ORD from RejectFile)
Order by ShipDatre, ORD, LINE
- Program reads through cursor
- If an order is ship-complete and the OnHand-AccumDemand<0 add the order to the "RejectFile"
- Close and reopen cursor and loop to first step until no more reject file additions.
midrange.com
