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
(PARTITION BY Table1.P_ID, Table1.DIM_ID)
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
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
WHERE job_id = ’PU_CLERK’;