accumulate into weekly buckets

[freak]

Using 9.1d.

I need to go through some records and accumulate a value into a weekly bucket based on a record date field. I thought about something like this but the compiler complains about the function I have in the last-of function.

WeekNumber is a function that returns the number of the week within year.

for each mytable break by WeekNumber(mytable.mydate) no-lock:
    
    if last-of(WeekNumber(mytable.mydate)) then do:
       do stuff
   end.
end.

Here is the error message...

** BREAK keyword or BY phrase missing for aggregate expression. (574)

Any tips on how to make this work?

 

[GregTomkins]

The average quality of questions on this forum is really atrocious.

 

[freak]

Why Greg? What did I say to offend you?

 

[newprog]

BREAK

Over a series of block iterations, you might want to do some work based on whether the value of a certain field changes. This field defines a break group. For example, you might be accumulating some value, such as a total. You use the BREAK option to define state as the break group.

You are not breaking on a field but on a function????

try build a temp-table with the weekday in it & then breaking on that

 

[KnutHandsome]

The average quality of questions on this forum is really atrocious.

You should probably establish some rep here before dismissing other posters.

This question is not one of the atrocious ones (though ProgressTalk is not short of those) for at least 3 reasons:

• It's quite understandable to think you can order by the results of a function and do something when you get to the last record in each group.
• The syntax is valid for Progress' in-built functions.
• If the UDF is valid syntax in the BREAK BY, why not in the LAST-OF?

Is ProgressTalk really that bad for a technology-specific forum? JOS (assuming you are that Greg) may be atypical.

You sound jaded.

 

[freak]

BREAK

Over a series of block iterations, you might want to do some work based on whether the value of a certain field changes. This field defines a break group. For example, you might be accumulating some value, such as a total. You use the BREAK option to define state as the break group.

You are not breaking on a field but on a function????

try build a temp-table with the weekday in it & then breaking on that

I did that but I was hoping there was something I was missing that would allow the last-of function to work. Kinda like in Report Builder where you can set an aggregate as a pre-pass.

Thanks!

 

[freak]

You should probably establish some rep here before dismissing other posters.

This question is not one of the atrocious ones (though ProgressTalk is not short of those) for at least 3 reasons:

• It's quite understandable to think you can order by the results of a function and do something when you get to the last record in each group.
• The syntax is valid for Progress' in-built functions.
• If the UDF is valid syntax in the BREAK BY, why not in the LAST-OF?

Is ProgressTalk really that bad for a technology-specific forum? JOS (assuming you are that Greg) may be atypical.

You sound jaded.

Thank you! You are quite amazing as you hit the nail right on the head with your "3 reasons"!!

 

[TomBascom]

I'll add a few more

It is a well written question with a clear subject that actually relates to the content of the post.

The question clearly shows a simple test case that the poster is struggling with.

The posting is formatted cleanly with CODE blocks used.

The version of Progress in use is specified.

(BTW, 9.1D is ancient and unsupported. There will never be a bug fix. You need to upgrade.)

 

[leite1969]

O NO-LOCK não pode estar depois do BREAK-BY, coloque o NO-LOCK depois do MYTABLE e verifique se continua o erro.


for each mytable no-lock
break by WeekNumber(mytable.mydate):

if last-of(WeekNumber(mytable.mydate)) then do:
do stuff
end.

end.


Att.


Eduardo Leite

 

[freak]

I'll add a few more

It is a well written question with a clear subject that actually relates to the content of the post.

The question clearly shows a simple test case that the poster is struggling with.

The posting is formatted cleanly with CODE blocks used.

The version of Progress in use is specified.

(BTW, 9.1D is ancient and unsupported. There will never be a bug fix. You need to upgrade.)

Our ERP system is compiled against 9.1d and we cannot upgrade till we remove our dependency on Report Builder. Unfortunately we have 100's of RB reports and no tools to convert them to something like Crystal.

 

[freak]

O NO-LOCK não pode estar depois do BREAK-BY, coloque o NO-LOCK depois do MYTABLE e verifique se continua o erro.

Can anyone translate this?

 

[TomBascom]

You should be able to get to 9.1E without needing to replace RB.

 

[leite1969]

Sorry, I'm Brazilian and I do not know much English, I (translated by Google) speak more clear:

The attribute is NO-LOCK BREAK-BY after, put the NO-LOCK table after and before the break-BY.

 

[freak]

You should be able to get to 9.1E without needing to replace RB.

What improvement would 9.1E be?

 

[TomBascom]

1) It's actually supported (sort of anyway).

2) Lots of bug fixes since 9.1D

3) Some very significant performance improvements. Many 4GL statements were improved in the late 9.1D and 9.1E service packs. Lots of stuff got 25% or more faster.

4) Far better SQL-92 (ODBC) drivers.

5) You'll get a chance to familiarize yourself with ordering an upgrade from your sales rep. (Ok, that might not be a good thing. But better to do it now than later.)

6) I'll stop bugging you about being on a totally unsupported release and just pester you to get to OpenEdge 10