progressive sum in db2 sql

I believe DB2 supports the ANSI Windowing functions with ROWS|RANGE framing. Therefore, you can give this a try.

SUM(YourValueColumn)
   OVER
   (
   ORDER BY YourDateColumn
   ROWS UNBOUNDED PRECEDING
   )

OR

SUM(YourValueColumn)
   OVER
   (
   ORDER BY YourDateColumn
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   )

Reference: OLAP functions
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0023461.htm

Hope that helps!

could you write the exact code?
Starting from this sql:
select fDate, fQty, ***** progressive sum of fQty ***** from LittleWarehouse

thanks,

select fDate, fQty
     , sum(fQty) over(order by fDate rows unbounded preceding) fQty_RT
from LittleWarehouse
;

Select allOpen in new window

very strange error,
it gives me: invalid token (,
, (comma) from into expected....

(after Over doesn't expect "("

Hi Bob,


Let's let one of our AS/400 experts chime in.  I don't know when DB2 on the AS/400 added the OLAP functions.


Kent

ok,
thanks ;)

you can always achieve this using a join, for example

select t1.date_col, sum(t2.val_col)
from your_table t1
  join your_table t2
    on t1.date_col <= t2.date_col
order by t1.date_col

I don't understand what kind of data are there in t2 or in t1?

Nice point, Kent. I thought the version cited was equivalent to UDB 9, but that was going off the a bad assumption that one it is all the features and two that version did indeed have full support for windowing. Yes, a join or sub query is possible. Since you have to use your table LittleWarehouse more than once in the query, you have to alias them so SQL will know which you are referring to when you call for column fQty for example.

I think you want GROUP BY fDate, fQty using the JOIN approach also.

No real help from me. I'm only noting that V5R4 was initial OLAP support in the standard DB2 on AS/400s. (It was available earlier, but for extra cost.) Detailed syntax isn't quite the same inside of OVER(), and I don't have a working example for this case. Nor am I clear on exactly what the statement should be in V5R4 -- I personally only have V5R3 available for my experimentation.

Tom

This ins't really an answer to the question, but a note about documentation.

I noticed a documentation link above in mwvisa's post to the "DB2 UDB LUW OLAP Function Guide".  The
"LUW" stands for Linux, Unix, and Windows, not the AS/400 (iSeries, i5, IBM i).  Unfortunately, AS/400 DB2 differs significantly on some points from LUW and DB2 for z, so readers will save a lot of frustration if they use the appropriate documentation for the DB2 version they are using.

DB2 for AS/400 / iSeries / IBM i information is found in the IBM (AS/400, iSeries, i) Information Center for your release, under the Database topic.  This is the link for the V5R4 Information Center:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp

The "What's New" topic in the Database section has a great introduction to the OLAP features added in V5R4, with examples.

If you look at the list of Database - Printable PDF's, you'll find the SQL Programmer's Guide, and the SQL Reference, which are the two "main" SQL references for the system:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafykickoff.htm

The process for other OS versions is similar: Go to the Information Center for your OS Version (google "IBM Information Center V6R1", for example), expand the Database topic, and look for the SQL Programming and SQL Reference manuals.

- Gary Patterson

Very nice, Gary! It appears from the documentation, OLAP support was added for the basic windowing functions but NOT with the framing which makes the running totals work as I showed; therefore, the JOIN approach will have to be used. If you have a large row set, it will be a little inefficient; however, I am sure the AS/400 has plenty of processing power to handle that ... :)

@momi_sabag and daveslash
Sorry, but your code, which is so easy,
doesn't work in my v5r4m0 os400 machine.

thanks

Glad you figured that out. I guess on 9/30, I should have been more clear that what I was saying is the join approach would not work without the group by. Anyway, happy it is all working for you now.

--Kevin

Sorry mwvisa1.
I have tried to change points attribution but it's difficult.
I ask to the moderator to do so for me.
thank you everybody.

Not a problem. I posted that more as an apology for you as it could have saved you six days. I meant what I said, though, the important part is you got a working solution.

Best regards and happy coding,

Kevin