We have an existing reporting functionality. For the sake of simplicity let's assume below is the query:
select * from tab1
where
col1 = 'ABC'
and col2 = 'XYZ'
and col3 = 'KLM'
We now have a requirement to create an exception report. This report will contain all the records which were filtered out by the previous report, along with the reason (e.g. col1 <> 'ABC or col2 <> 'XYZ' and so on).
Of course, we can use the MINUS operator 3 times with 3 separate queries having each condition once compared with the original query to get differential records along with the precise reason why the row was filtered out. Like below query will give out all the rows that were filtered out with reason col1 <> 'ABC. We repeat this 2 more times to get the rows filtered out for the other 2 conditions.
select * from tab1
where
col2 = 'XYZ'
and col3 = 'KLM'
MINUS
select * from tab1
where
col1 = 'ABC'
and col2 = 'XYZ'
and col3 = 'KLM'
But our problem is that reporting query is much more complex with joins which also have conditions, and repeating a MINUS query for each condition seems very tedious and bad design.
Is there any way we can solve this problem with a simpler approach?
