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.
