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.

Monday, October 20, 2008

Find Discrepancies with 'Distinct Count Over'

In this query, I'm looking for discrepancies in data. In particular, I want to find column names in the data dictionary which have more than one type of data assigned. This is done by asking for a distinct count of data_types within a partition based on column_names. The code then eliminates anything that has a count of one.
Select *
From
(
Select Table_Name,
Column_Name,
Data_Type,
Count(Distinct Data_Type)
Over(Partition By Column_Name)
As
Cnt
From All_Tab_Columns
Where Table_Name Like 'USER_%'
)
Where Cnt >1
Order By Column_Name;

No comments:

Post a Comment