Useful functions in Oracle 11g Part 04

Introduction

In this article, i have given another few no of useful functions and alternatives way  demonstration in oracle 11g.

1. Triangular Series
2. Rollup Query
3. Differece between wm_concat and ListAgg

Triangular Series

http://nimishgarg.blogspot.com/2010/07/oracle-sql-triangular-series-1-3-6-10.html

Example Scenario

11+2=3(1+2)+3=6(1+2+3)+4=10(1+2+3+4)+5=15

The general representation of a triangular number is dn= 1 + 2 + 3 + 4 +...+ (n-2) + (n-1) + n,

SQL for generating Triangular Series

select sum(b) from(select level a from dual connect by level < 10) x,(select level b from dual connect by level < 10) y
where b<=agroup by aorder by 1;

 OUTPUT 

Rollup Query

http://nimishgarg.blogspot.com/2010/07/oracle-rollup-query.html

ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.

selectdeptno,job,sum(sal)fromscott.emp
group byrollup(deptno,job);

Output

Differece between wm_concat and ListAgg

http://nimishgarg.blogspot.com/2010/07/oracle-differece-between-wmconcat-and.html

1. wm_concat is undocumented and unsupported by oracle, thus rendering production systems unsupported. listagg is documented and supported by oracle.

2. wm_concat allows distinct option. listagg does not allows it.

select wm_concat(distinct ename) as enames from emp;

3. listagg allows to decide string concat order. wm_concat does not allows it.

select listagg(ename,',') within group(order by empno desc) as enames from emp;

4. listagg allows to decide delimiter. wm_concat does not allows it.

select listagg(ename,'***') within group(order by empno desc) as enames from emp;

5. wm_concat allows to be used olap function with order by listagg does not allows it. listagg allows only olap function without order by.

select deptno,wm_concat(ename) over(order by deptno) as enames from emp;

6. wm_concat allows to be used keep. listagg does not allows it.

select wm_concat(ename) keep(dense_rank first order by deptno) as enames from emp;

 That's all. enjoy,i will post more functions and useful queries in future.