I apologize as I am very inexperienced and struggling. Any help would be appreciated. I am trying create a query with a sum/accumulation that returns based on a date descending and only so may rows. I have tried several things and can't seem to put them all together. If I was writing a SQL query here is how I would write the query. select a.custno, rownum, a.dateeff, a.pid, a.ptotal from (select a.custno custno, a.dateeff dateeff, a.batchid || ' ' || a.docid pid, sum(a.value) ptotal from tran a where a.type = '10' group by a.custno, a.dateeff, a.batchid || ' ' || a.docid ) a where a.custno = '224755' and rownum
As MadDBA says -- forget SQL.
Trying to make Progress be SQL only leads to agony and frustration.
I have no idea what your databases schema is so I will use "sports2000". The important elements that you probably want to become familiar with first are below:
define variable stateCount as integer no-undo.
for each customer no-lock break by customer.state:
if first-of( customer.state ) then
stateCount = 0.
stateCount = stateCount + 1.
if last-of( custome.state ) then
display stateCount.
end.
This will provide a list of "states" and the count of customers per state. There are statements like COUNT and ACCUM that automate some of this but I never use them. They're finicky and more trouble than they are worth. IMHO.
What have you tried so far in terms of Progress?
Step 1: Forget almost everything about SQL :)
Look at the progress documentation for the following
FOR EACH
BREAK-BY
As MadDBA says -- forget SQL.
Trying to make Progress be SQL only leads to agony and frustration.
I have no idea what your databases schema is so I will use "sports2000". The important elements that you probably want to become familiar with first are below:
define variable stateCount as integer no-undo.
for each customer no-lock break by customer.state:
if first-of( customer.state ) then
stateCount = 0.
stateCount = stateCount + 1.
if last-of( custome.state ) then
display stateCount.
end.
This will provide a list of "states" and the count of customers per state. There are statements like COUNT and ACCUM that automate some of this but I never use them. They're finicky and more trouble than they are worth. IMHO.
def var totalValue like tran.value. /* damn I hate like, use the correct data type instead */
FOR EACH tran WHERE tran.custno = '10224755' and tran.type = '10' BREAK BY tran.batchid BY tran.docid:
totalValue = totalValue + tran.value.
if last-of(tran.docid) then do:
DISPLAY tran.custno
tran.dateeff
tran.batchid + ' ' + tran.docid
totalValue.
totalValue = 0.
end.
END.
- See more at: community.progress.com/.../61897.aspx
SQL does not support “rownum” . Use “ROWID”, If you are looking for rowid of each row.
If you are not looking for the row-id , interested in sequential numbers for each row then use sequences.
SQL does not support “rownum” . Use “ROWID”, If you are looking for rowid of each row.
If you are not looking for the row-id , interested in sequential numbers for each row then use sequences.
Flag this post as spam/abuse.
I want to thank everyone. All of this information was very helpful and has given us a new level of understanding. We are still learning, but this one was a GREAT help.
[mention:825fc31ee1cd4ff681ed68b631c5eced:e9ed411860ed4f2ba0265705b8793d05] Great to hear our Community helped you. Could you flag the question as answered? Thanks.
Also we have an OpenEdge Development group with a forum. I'll move this thread to it and would suggest you could join and post this kind of ABL/Development type of questions. Cheers
Here the link to the OE Dev group