Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003

    Unanswered: Analytical Function in DB2.....


    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:

    (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’;

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

  2. #2
    Join Date
    Nov 2004
    Provided Answers: 4
    You could write:
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts