On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Friday, October 24, 2008

HR Tables: Keys Used in Joins

Column Countries   Format A15
Column Departments Format A15
Column Employees Format A15
Column Jobs Format A15
Column Job_History Format A15
Column Locations Format A15
Column Regions Format A15

Set Markup Html On Entmap On Spool On Preformat Off
Set Term Off

Spool Fk.Htm

Set Heading Off
Column Title Entmap Off
Select '<Center><H1>Hr Tables : Keys Used In Joins</H1></Center>' As Title
From Dual;

Set Heading On
Select Max(Case When Table_Name = 'EMPLOYEES' Then Column_Name End) As EMPLOYEES,
Max(Case When Table_Name = 'JOBS' Then Column_Name End) As JOBS,
Max(Case When Table_Name = 'JOB_HISTORY' Then Column_Name End) As JOB_HISTORY,
Max(Case When Table_Name = 'DEPARTMENTS' Then Column_Name End) As DEPARTMENTS,
Max(Case When Table_Name = 'LOCATIONS' Then Column_Name End) As LOCATIONS,
Max(Case When Table_Name = 'COUNTRIES' Then Column_Name End) As COUNTRIES,
Max(Case When Table_Name = 'REGIONS' Then Column_Name End) As REGIONS
From (
Select *
From (
Select Table_name, Initcap(Column_name) Column_name ,
Count(*) Over (Partition By Column_name
Order By Column_name) Colctr
From User_tab_columns
Where Table_name not in ( 'TEST' , 'EMP_DETAILS_VIEW')
)
where Colctr <> 1
)
Group By Column_name
Order By Column_name;

Spool Off

Set Markup Html Off Entmap Off Spool Off Preformat Off
Set Term On

PROMPT ** Report Completed. Use Mozilla to view your output: FK.HTM **

No comments:

Post a Comment