SQL & PL/SQL

Hi All,

I have a piece of code where in I am using UNION ALL clause to cater my requirements. The first column which is a description is varying based on the where conditions. For example,

SELECT 'Column 2 is not Matching' Difference_Desc,
TBL1.COL1,
TBL1.COL2,
TBL1.COL3
TBL2.COL1,
TBL2.COL2,
TBL2.COL3
FROM TBL1, TBL2
WHERE TBL1.COL1=TBL2.COL1
AND TBL1.COL2 != TBL2.COL2
UNION ALL
SELECT 'Column 3 is not Matching' Difference_Desc,
TBL1.COL1,
TBL1.COL2,
TBL1.COL3
TBL2.COL1,
TBL2.COL2,
TBL2.COL3
FROM TBL1, TBL2
WHERE TBL1.COL1=TBL2.COL1
AND TBL1.COL3 != TBL2.COL3;

In the above query COL1 of each table acts as a unique identifier and rest of the columns needs to be compared, and it gives me the right results. But my problem is, I have few set of tables which have approx 45-50 cols in each. And have to find out the differences in each col across the two tables and I want to avoid writing UNION ALL query for all 45-50 columns, instead looking for a single query which captures the differences.

I tried using CASE and WHEN clause in order to avoid UNION ALL. For example,

SELECT Difference_Desc,
TBL1.COL1,
TBL1.COL2,
TBL1.COL3
TBL2.COL1,
TBL2.COL2,
TBL2.COL3
FROM (SELECT
(CASE WHEN TBL1.COL2 != TBL1.COL2
THEN 'Column 2 is not Matching'
WHEN TBL1.COL3 != TBL1.COL3
THEN 'Column 3 is not Matching'
END) Difference_Desc,
TBL1.COL1,
TBL1.COL2,
TBL1.COL3
TBL2.COL1,
TBL2.COL2,
TBL2.COL3
FROM TBL1, TBL2
WHERE TBL1.COL1=TBL2.COL1
AND (TBL1.COL2 != TBL2.COL2
OR TBL1.COL3 != TBL2.COL3));

Using the above query it results unique records, like, if there are differences both in col2 and col3 in a particular record, then using UNION ALL I was getting two separate records, one with a description 'Col2 not matching' and other with a description 'Col3 not matching'. But its not the case with the above CASE and WHEN clasue, its not resulting me the right set of records.

Please suggest me the right approach to do the same.
The Database is Oracle 10g.

Thanks,
Saurabh.

Edited by: 848265 on 29-Mar-2011 06:42