OLAP Function in DB2......

[rkumar28]

Hi,

I have a question regarding the DB2 UDB 8.1. I have a query in Oracle 9i that needs to be rewritten on DB2(UDB).

This Oracle query is using an OLAP Analytic function RATIO_TO_REPORT. I am trying to write this in DB2 as DB2(UDB) doesn't have this function so far. I have tried RATIOTOREPORT but doesn't seems to work on DB2.

Is there a way to write the query below for DB2. My query is my Oracle Query:

Select TM_DTE, SLS_AMT,P_ID,DIM_ID
RATIO_TO_REPORT(Table1.SLS_AMT) OVER
(PARTITION BY Table1.P_ID, Table1.DIM_ID)
from Table1

Will appreciate any help in this regard.....


Below is a small excerpt about RATIO_TO_REPORT function on Oracle side:

"RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the
sum of a set of values. If expr evaluates to null, then the ratio-to-report value also
evaluates to null.
The set of values is determined by the query_partition_clause. If you omit
that clause, then the ratio-to-report is computed over all rows returned by the query.
You cannot use RATIO_TO_REPORT or any other analytic function for expr. That is,
you can use other built-in function expressions for expr, but you cannot nest
analytic functions....

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS RR
FROM employees
WHERE job_id = ’PU_CLERK’;

LAST_NAME SALARY RR
------------------------- --------------- ----------
Khoo 3100 .223021583
Baida 2900 .208633094
Tobias 2800 .201438849
Himuro 2600 .18705036
Colmenares 2500 .179856115
"

 

[BrianTyler]

sls_amt / SUM(sls_amt) OVER() AS ratio_amt

is the nearest equivalent I know.

For full details of the OLAP (and other SQL functionality) you must look at Graeme Birchell's cookbook.


regards

Brian