OLAP Functions in i-Series

 Hi Guys, 

One of my Linkedin friend asked me about OLAP Functions in i-Series, So just thought of writing a brief on OLAP.

OLAP functions in i-Series

 IBM gave developers an impressive set of Online Analytical Processing (OLAP) tools to aid with common ranking and numbering issues. The significance of these OLAP tools lies in the amount of work they do and the time it will save database programmers.

ROW_NUMBER

To start, the ROW_NUMBER is a huge blessing as it finally furnishes SQL with a built-in counter function. Here's a trivial example that adds a row number column to a result set:

Insert Into OrderItm

Select OrderNo,

       Row_Number() Over (Partition By OrderNo

                          Order By OrderNo,ItemNo),

       ItemNo,

       QtyOrdered,

       SalePrice

In this scenario, ROW_NUMBER will continually increment the ItemSeq field over all orders that are being imported and counter would get reset for every change in order number.