How to get count of mismatching records

Database

How to get count of mismatching records

Hi,

I am having two tables each having 20 columns. I want to compare these two tables table1 and table2 to get the count of all the records in table1 and all therecord in table2 and the differnece of records in a single query. Is it possible in Teradata 12??

Tags (3)
1 REPLY

Re: How to get count of mismatching records

Hi,

Following query will give you the rows which are different in the two tables:

SELECT * FROM (SELECT Employee_Number, Dept_Number, Emp_Mgr_Number, Job_Code,

Last_Name, First_Name, Salary_Amount,'only in emp1' AS table_status FROM emp1

EXCEPT

SELECT Employee_Number, Dept_Number, Emp_Mgr_Number, Job_Code,

Last_Name, First_Name, Salary_Amount,'only in emp1' AS table_status FROM emp2) A

UNION ALL

SELECT * FROM (SELECT Employee_Number, Dept_Number, Emp_Mgr_Number, Job_Code,

Last_Name, First_Name, Salary_Amount,'only in emp2' AS table_status FROM emp2

EXCEPT

SELECT Employee_Number, Dept_Number, Emp_Mgr_Number, Job_Code,

Last_Name, First_Name, Salary_Amount,'only in emp2' AS table_status FROM emp1) B;

For the counts of both the tables and the difference, execute the following query:

SELECT COUNT(*),'count of emp1' AS status FROM emp1

UNION ALL

SELECT COUNT(*),'count of emp2' AS status FROM emp2

UNION ALL

SELECT COUNT(*),'not in Emp2' AS tb_name FROM (SELECT Employee_Number, Dept_Number, Emp_Mgr_Number, Job_Code,

Last_Name, First_Name, Salary_Amount,'only in emp1' AS table_status FROM emp1

EXCEPT

SELECT Employee_Number, Dept_Number, Emp_Mgr_Number, Job_Code,

Last_Name, First_Name, Salary_Amount,'only in emp1' AS table_status FROM emp2) A

UNION ALL

SELECT COUNT(*),'not in Emp1' AS tb_name FROM (SELECT Employee_Number, Dept_Number, Emp_Mgr_Number, Job_Code,

Last_Name, First_Name, Salary_Amount,'only in emp2' AS table_status FROM emp2

EXCEPT

SELECT Employee_Number, Dept_Number, Emp_Mgr_Number, Job_Code,

Last_Name, First_Name, Salary_Amount,'only in emp2' AS table_status FROM emp1) B;

-Sidd