ChkDB |
Open Source Rule-Based Data Checking |
|
Home Contact Download Releases Open Source License Project Contributing The PL/SQL Using ChkDB Installation Read-Me Rule-Sets ChkDB Tables E-R Diagram
Tiny Sample External Links Data Quality and Data Profiling (a glossary) |
Sample QueriesThis is the sample_queries.sql file that comes with a ChkDB distribution (without the license section). To execute these sample queries, sending the results to a file...
set linesize 70
set trimspool on
spool my_results.txt
-- ==============================================================
prompt ====================================================
prompt ChkDB Sample Queries
prompt
-- Before we begin...
set pagesize 999
set linesize 70
set trimspool on
set trimout on
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Summary of Active Rules in a Ruleset
select r.TABLE_NAME,
r.RULE_ID, substr(r.RULE_CLASS,1,5) CLASS,
r.DESCRIPTION
from CHKDB_DOMAIN d, CHKDB_TABLE t,
CHKDB_RULESET s, CHKDB_RULE r
where s.DATA_DOMAIN = d.DATA_DOMAIN
and s.DATA_DOMAIN = t.DATA_DOMAIN
and r.DATA_DOMAIN = s.DATA_DOMAIN
and r.RULESET_ID = s.RULESET_ID
and r.TABLE_NAME = t.TABLE_NAME
and d.ACTIVE_YN = 'Y'
and t.ACTIVE_YN = 'Y'
and s.ACTIVE_YN = 'Y'
and r.ACTIVE_YN = 'Y'
and r.DATA_DOMAIN = 'CHKDB_SAMP_HR'
and r.RULESET_ID = 'SAMP_HR_RULES'
order by 1, 2;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Summary of In-Active Rules in a Ruleset
select r.TABLE_NAME,
r.RULE_ID, substr(r.RULE_CLASS,1,5) CLASS,
r.DESCRIPTION
from CHKDB_DOMAIN d, CHKDB_TABLE t,
CHKDB_RULESET s, CHKDB_RULE r
where s.DATA_DOMAIN = d.DATA_DOMAIN
and s.DATA_DOMAIN = t.DATA_DOMAIN
and r.DATA_DOMAIN = s.DATA_DOMAIN
and r.RULESET_ID = s.RULESET_ID
and r.TABLE_NAME = t.TABLE_NAME
and (d.ACTIVE_YN = 'N' or
t.ACTIVE_YN = 'N' or
s.ACTIVE_YN = 'N' or
r.ACTIVE_YN = 'N')
and r.DATA_DOMAIN = 'CHKDB_SAMP_HR'
and r.RULESET_ID = 'SAMP_HR_RULES'
order by 1, 2;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Result count by table and rule...
prompt (raw data for Data Quality Profiling)
select a.TABLE_NAME,
a.RULE_ID,
substr(b.RULE_CLASS,1,5) CLASS,
substr(count(*),1,6) OCCURS
from CHKDB_RESULT a, CHKDB_RULE b
where a.DATA_DOMAIN = b.DATA_DOMAIN
and a.TABLE_NAME = b.TABLE_NAME
and a.RULESET_ID = b.RULESET_ID
and a.RULE_ID = b.RULE_ID
and a.DATA_DOMAIN = 'CHKDB_SAMP_HR'
group by a.TABLE_NAME, a.RULE_ID, b.RULE_CLASS
order by a.TABLE_NAME, a.RULE_ID;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Results by table and rule, ordered by number
prompt of occurrences, with description...
prompt (crude data quality profiling report)
select a.TABLE_NAME,
substr(b.RULE_CLASS,1,5) CLASS,
substr(count(*),1,6) OCCURS,
b.DESCRIPTION
from CHKDB_RESULT a, CHKDB_RULE b
where a.DATA_DOMAIN = b.DATA_DOMAIN
and a.TABLE_NAME = b.TABLE_NAME
and a.RULESET_ID = b.RULESET_ID
and a.RULE_ID = b.RULE_ID
and a.DATA_DOMAIN = 'CHKDB_SAMP_HR'
group by a.TABLE_NAME, b.RULE_CLASS, b.DESCRIPTION
order by a.TABLE_NAME, count(*) desc;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Check-Result Details (Max 100)
select a.TABLE_NAME, a.DATA_DOMAIN, a.RULESET_ID,
a.RULE_ID,
rpad(a.CHECK_DATE,10) CHECK_DATE,
substr(r.RULE_CLASS,1,5) CLASS,
r.DESCRIPTION,
a.SELECTED_ROWID,
substr(a.ROW_PRIMARY_KEY,1,60) PRIMARY_KEY,
a.ROW_OUT_DESC,
a.ROW_OUT_DATA
from CHKDB_RESULT a, CHKDB_RULE r
where a.DATA_DOMAIN = r.DATA_DOMAIN
and a.RULESET_ID = r.RULESET_ID
and a.TABLE_NAME = r.TABLE_NAME
and a.RULE_ID = r.RULE_ID
and a.DATA_DOMAIN = 'CHKDB_SAMP_HR'
and rownum < 101
order by 1, 2, 3, 6 desc;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Various Other Sample Queries
prompt
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt All Active Rules in a Domain
select r.TABLE_NAME, r.DATA_DOMAIN, r.RULESET_ID,
r.RULE_ID, substr(r.RULE_CLASS,1,5) CLASS,
r.DESCRIPTION
from CHKDB_DOMAIN d, CHKDB_TABLE t,
CHKDB_RULESET s, CHKDB_RULE r
where s.DATA_DOMAIN = d.DATA_DOMAIN
and s.DATA_DOMAIN = t.DATA_DOMAIN
and r.DATA_DOMAIN = s.DATA_DOMAIN
and r.RULESET_ID = s.RULESET_ID
and r.TABLE_NAME = t.TABLE_NAME
and d.ACTIVE_YN = 'Y'
and t.ACTIVE_YN = 'Y'
and s.ACTIVE_YN = 'Y'
and r.ACTIVE_YN = 'Y'
and s.DATA_DOMAIN = 'CHKDB_SAMP_HR'
order by 1, 2, 3 desc, 6;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Tables and Primary Keys in the CHKDB_SAMP_HR Domain
prompt (only 1st 28 chars of table and column names)
select TABLE_NAME TABLE_NAME,
rpad(CHECKABLE_YN,9) CHECKABLE,
rpad(decode(TABLE_VIEW_TV,
'T', 'Table',
'V', 'View',
'Unknown'),10) TABLE_VIEW,
rpad(ACTIVE_YN,6) ACTIVE,
PRIMARY_KEY_LIST PRIMARY_KEY_LIST
from CHKDB_TABLE
where DATA_DOMAIN = 'CHKDB_SAMP_HR'
order by 2 desc, 1;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt To see which tables in a domain are active...
select t.TABLE_NAME, t.ACTIVE_YN "?",
d.DATA_DOMAIN, d.ACTIVE_YN "?"
from CHKDB_DOMAIN d, CHKDB_TABLE t
where t.DATA_DOMAIN = d.data_domain
and t.DATA_DOMAIN = 'CHKDB_SAMP_HR'
order by 1, 3;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt To see which rules in a table are active...
prompt
prompt Rules for table CHKDB_SAMP_HR_DEPT
prompt in domain CHKDB_SAMP_HR...
select s.RULESET_ID, s.ACTIVE_YN "?",
r.RULE_ID, r.ACTIVE_YN "?"
from CHKDB_RULESET s, CHKDB_RULE r
where r.DATA_DOMAIN = s.DATA_DOMAIN
and r.RULESET_ID = s.RULESET_ID
and r.DATA_DOMAIN = 'CHKDB_SAMP_HR'
and r.TABLE_NAME = 'CHKDB_SAMP_HR_DEPT'
order by 1, 3;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Count of Results by Table...
select TABLE_NAME, count(*) PROBLEMS
from CHKDB_RESULT
where DATA_DOMAIN = 'CHKDB_SAMP_HR'
group by TABLE_NAME
having count(*) > 0;
prompt = = = = = = = = = = = = = = = = = = = = = = = = = =
prompt Counts from each ChkDB table...
select count(*) CHKDB_DOMAIN_COUNT from CHKDB_DOMAIN;
select count(*) CHKDB_TABLE_COUNT from CHKDB_TABLE;
select count(*) CHKDB_RULESET_COUNT from CHKDB_RULESET;
select count(*) CHKDB_RULE_COUNT from CHKDB_RULE;
select count(*) CHKDB_RESULT_COUNT from CHKDB_RESULT;
select count(*) CHKDB_RULE_SQL_COUNT from CHKDB_RULE_SQL;
select count(*) CHKDB_RULE_ERR_COUNT from CHKDB_RULE_ERR;
prompt ====================================================
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
|