Analytical Function in DB2.....

rkumar28 · #1 (**permalink**) 07-26-06, 15:57

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
"

Wim · #2 (**permalink**) 07-27-06, 05:00

You could write:

Code:

SELECT last_name, salary, salary/T.sum_salary AS rr
FROM employees,
     (SELECT SUM(salary) as sum_salary 
      FROM employees
      WHERE job_id = ’PU_CLERK’
     ) as T
WHERE job_id = ’PU_CLERK’;

Perhaps there's a more efficient way to do it, but this will do the job.

Internet Services

Web Development

Digital Marketing

Consumer Tech