SQL> SELECT 1 my_number
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_number
  5    FROM DUAL
  6  /
SELECT 1 my_number
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression


SQL> SELECT SYSDATE my_date
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_date
  5    FROM DUAL
  6  /
SELECT SYSDATE my_date
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

SQL> SELECT 'C' my_char
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_char
  5    FROM DUAL
  6  /

M
-
C

SQL> SELECT 1 my_number
  2    FROM DUAL
  3  UNION
  4  SELECT TO_NUMBER (NULL) my_number
  5    FROM DUAL
  6  /

 MY_NUMBER
----------
         1


SQL> SELECT SYSDATE my_date
  2    FROM DUAL
  3  UNION
  4  SELECT TO_DATE (NULL) my_date
  5    FROM DUAL
  6  /

MY_DATE
---------
15-JUN-06

SQL> SELECT 1 my_number
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_number
  5    FROM DUAL
  6  /

 MY_NUMBER
----------
         1


SQL> SELECT SYSDATE my_date
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_date
  5    FROM DUAL
  6  /

MY_DATE
---------
15-JUN-06

Cool SQL Analytic Function: RATIO_TO_REPORT

RATIO_TO_REPORT is a very handy and useful function. Before going through what it does, let’s first look at this example:

SELECT   last_name, salary,
         ROUND (salary / SUM (salary) OVER () * 100,
                2
               ) percent_of_total
    FROM employees
   WHERE job_id = 'PU_CLERK'
ORDER BY last_name;

Returns:

        LAST_NAME           SALARY              PERCENT_OF_TOTAL            
------------------------- ---------- -------------------------------------- 
Baida                           2900                                  20.86 
Colmenares                      2500                                  17.99 
Himuro                          2600                                  18.71 
Khoo                            3100                                   22.3 
Tobias                          2800                                  20.14 

5 row(s) retrieved

The percent_of_total column represents the salary for each employee as a percentage of the total salary paid to all clerks. The above query can also be re-written using the RATIO_TO_REPORT SQL function:

SELECT   last_name, salary,
         ROUND
            (ratio_to_report (salary) OVER () * 100,
             2
            ) AS percent_of_total
    FROM employees
   WHERE job_id = 'PU_CLERK'
ORDER BY last_name

Both queries return the same output.

Oracle documentation definition: The RATIO_TO_REPORT function computes the ratio of a value to the sum of a set of values. Its syntax is:

RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )

In this, the following applies:

• expr can be any valid expression involving column references or aggregates.

• The PARTITION BY clause defines the groups on which the RATIO_TO_REPORT function is to be computed. If the PARTITION BY clause is absent, then the function is computed over the whole query result set.

Finally, Tom Kyte used the RATIO_TO_REPORT function to calculate the percentage of each browser hitting his blog and to calculate the percentage of his posts per month. Sergio Leunissen used the RATIO_TO_REPORT function as a basis for an APEX (HTML DB) report.

SQL Analytic functions are simple and powerful.

Filed in Oracle, Tips with 4 Comments | Tags: analytic, function, sql

---