Sunday, June 22, 2008

Unrelated Data Side-by-Side


To show unrelated data side by side, gather the data as separate queries adding a 'key' by using RowNum.

The parent query will then read the individual queries and match them on the key. As stated in the heading, the data from subquery one for key '1' is not related to the data from subquery two for key '1', etc.

Be sure to state a full outer join so no records are left behind on either side.
Column Key Noprint
Column Divider1 Heading ' '
Column Divider2 Heading ' '

Select Key,
Empno , Ename, '|' as Divider1,
Deptno, Loc , Dname, '|' as Divider2,
Grade , Losal, Hisal
From
-------------------------------
(
Select Rownum As Key,
Empno, Ename
From Emp
) Vte
-------------------------------
Full Outer Join
(
Select Rownum As Key,
Deptno, Loc, Dname
From Dept
) Vtd
Using (Key)
-------------------------------
Full Outer Join
(
Select Rownum As Key,
Grade, Losal, Hisal
From Salgrade
) Vts
Using (Key)
-------------------------------
Order By Key ;
See Oracle documentation for further detail:
Joins
Rownum

No comments:

Post a Comment