I'm having a problem with RunSum() not resetting the running total to zero before executing the SQL the 2nd time. In other words, it keeps the RunSum() value from the last time the SQL is executed so the running totals get bigger. Note: The underlying table has value(s) changed between running the SQL with RunSum(). The database is EDB v2.09 Unicode.

Example:
(Using EDBMgr v2.09 Build 1)

Open SQL tab #1 and do a simple query on the table
:
select * from players where team_id=1

Player_Name       Day_Num   Player_Scores  Team_Id
Dave                     1                    1               1
John                     2                    2               1
Steve                   1                     1               1

Open a 2nd Sql tab #2.

The first time the SQL is executed, it produced the correct result.

select team_id, Day_num, runsum(player_scores) from players  where team_id=1 group by day_num

team_id   Day_Num   RUNSUM of player_scores
1              1                      2
1              2                      4

Go back to SQL tab 1 and edit the player_scores for John to 22 and save the record.

Go to Sql tab 2 and re-execute the query that produces the running totals. (Do not change the SQL)

select team_id, Day_num, runsum(player_scores) from players  where team_id=1 group by day_num

Now I get

team_id    Day_Num   RUNSUM of player_scores
1                1                      6
1                2                     28

It should be 2 and 24, not 6 and 28. It is off by 4 which was the last RUNSUM() value from the previous SQL. So the RunSum() value was not reset.

Players table is:
Player_Name VarChar(10) UNI,
Day_Num      Integer,
Player_Scores Integer,
Team_ID        Integer


Barry

I found if I unprepare the query, the runsum() will produce the correct totals. So it may have had something to do with EDB caching the results.

So I'd recommend anyone using RunSum(), to unprepare the query before each execution otherwise your results may be wrong if the column being summed are changed between query executions.

Barry

Barry,

<< I'm having a problem with RunSum() not resetting the running total to
zero before executing the SQL the 2nd time. In other words, it keeps the
RunSum() value from the last time the SQL is executed so the running totals
get bigger. Note: The underlying table has value(s) changed between running
the SQL with RunSum(). The database is EDB v2.09 Unicode. >>

This is now fixed in the soon-to-be-released 2.10.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com