Delete rows

#1
Hello, is it posible to selecto some rows with condition where, and delete rows until sum of specific colum in rows is reach defined value.

FOR EACH tablename WHERE tablename.date EQ /mm/dd/yyyy
AND tablename.somthing > 0.

DELETE tablename (UNTIL sum of deleted tablename.somthing = X).


Somthing like that.
How to make that work. Its progress OE 10.1C.
 
#2
Progress 10.1c is ancient, obsolete and unsupported. You should upgrade. Actually you should have upgraded about 10 years ago.

None the less, your request is something that you have been able to do since the dark ages of Progress v2:
Code:
define variable limit as integer no-undo.
define variable sum as integer no-undo.

limit = 1000000.
sum = 0.

for each tablename exclusive-lock where somedate = mm/dd/yyyy:
  sum = sum + sometable.somefield.
  if sum >= limit then
    leave.
   else
    delete sometable.
end.
 
#3
Thank you Tom, this will delet each row with sometable.somefle >= limit, I need to delete (somefild in row 1+somefild in row 2+.......) >= limit

While deleting to sum value in deleted filds and when limit is reached to stop deliting.
 
Last edited:

Cringer

ProgressTalk.com Moderator
Staff member
#4
Your requirements really aren't making sense. My best advice is to go and write down the requirements clearly in English (or your native language). You'll then have a basis for building your logic. Until you understand the requirements and can explain them clearly you don't have a hope of writing the code.
 
#5
Perhaps, as Cringer suggests, your requirement statement is unclear.

Judging from the fragment of code that you posted you might be thinking that Progress is like SQL and that there is a magical WHERE clause that will do everything for you.

Progress is not like that. Progress does not try to do everything in a single statement. Progress is very simple. You build functionality procedurally - one step at a time.

The code that I posted accumulates the running total in "sum" and stops deleting rows when that sum exceeds the limit that you define.

Suppose, for instance, that your query on "sometable" returns 10 records that look like this (leaving out the date field):
Code:
id    somefield
--   ------------
1       10
2       15
3       12
4        1
5        8
6       10
7       20
8        3
9        3
10       90
If you set the "limit" variable in my code to 40 then rows 1 thru 4 will be deleted. At that point the "sum" variable will be 38, 38 plus 8 would be 46 which is >= 40 and the code will break out of the loop without deleting any more records.
 
#6
Exactly Tom, I need to delete 40, so 5th row need to update value to 6, and then i have -40. Or update value in all rows to get that -40 in total.

These are items on invoices, and when I do not have enough goods in my store I have to reduce the amount on my account. That's about 2k invoices a day. It does not matter how much I will reduce, I can even with every invoice, or with a few invoices all the amount.

I hope you understand my problem better.

Sory for my english, its my second language.

And thankyou for your fast response.
 
Top