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.

SQL400 for Beginners Part 3

Hi Guys,

I am back with 3rd tutorial of SQL400. After going through this blog, I hope all of you would be having basic understanding of SQL400

SQL ORDER BY

‘ORDER BY’ is used to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value.

The syntax for an ORDER BY statement is as follows:

SELECT "column_name" FROM "table_name" [WHERE "condition"] ORDER BY"column_name" [ASC, DESC]

It is possible to order by more than one column.

ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]

SELECT * FROM TBLSTR ORDER BY STRNAM

The above statement will select all records from file TBLSTR and sort them based upon the ascending values of column STRNAM