oracle tips & tricks

Oracle Tips & Tricks

 
Tuesday, 22 April 2008
Oracle SQL : Coding a matrix of display values
Oracle has a fascinating non-ANSI feature called an in-line view. The in-line view allows for a query to be placed into the FROM clause of an SQL statement, where you would normally place a table name.

By performing a SELECT in the FROM clause, we can summarize two columns, and display a third value based on two other columns.

In the example below, we take the employee name (ename) and the department number (deptno) and display the salary of the employee by their department.
prompt Display of Salary by Department



SELECT

*

FROM (SELECT

ename,

sum(decode(deptno,10,sal)) DEPT10,

sum(decode(deptno,20,sal)) DEPT20,

sum(decode(deptno,30,sal)) DEPT30,

sum(decode(deptno,40,sal)) DEPT40

FROM

emp

GROUP BY

ename)

ORDER BY 1;



Display of Salary by Department



ENAME DEPT10 DEPT20 DEPT30 DEPT40

---------- ---------- ---------- ---------- ----------

ADAMS 1100

ALLEN 1600

BLAKE 2850

CLARK 2450

FORD 3000

JAMES 950

JONES 2975

KING 5000

MARTIN 1250

MILLER 1300

SCOTT 3000

SMITH 800

TURNER 1500

WARD 1250


This type of query is very useful for Oracle data warehouse system where you must display information from a FACT table according to the values of two other columns.

Labels:

posted by popo @ 03:00  
0 Comments:
Post a Comment
<< Home
 
 
Search

Google
Previous Post
Category
Blogrush
Blogroll
Legal
Archives
Template by
Free Blogger templates