Get rows that were filtered out by a SQL query-CodePudding

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?

CodePudding user response：

Just put NOT round your filtering conditions:

select * from tab1
where
NOT (
  col1 = 'ABC'
  and col2 = 'XYZ'
  and col3 = 'KLM'

)

CodePudding user response：

With sample data like this:

WITH
    tbl AS
        (
            Select  1 "ID", 'ABC' "COL_1", 'X' "COL_2", 'KLM' "COL_3"   From Dual Union All
            Select  2 "ID", 'ABC' "COL_1", 'XYZ' "COL_2", 'KLM' "COL_3" From Dual Union All
            Select  3 "ID", 'A' "COL_1", 'XYZ' "COL_2", 'KLM' "COL_3"   From Dual Union All
            Select  4 "ID", 'ABC' "COL_1", 'XYZ' "COL_2", 'K' "COL_3"   From Dual Union All
            Select  5 "ID", 'ABC' "COL_1", 'XYZ' "COL_2", 'KLM' "COL_3" From Dual 
        )
--          ID COL_1 COL_2 COL_3
--  ---------- ----- ----- -----
--           1 ABC   X     KLM   
--           2 ABC   XYZ   KLM   
--           3 A     XYZ   KLM   
--           4 ABC   XYZ   K     
--           5 ABC   XYZ   KLM

Your report data would look like:

SELECT  * 
FROM    tbl
WHERE   COL_1 = 'ABC' and COL_2 = 'XYZ' And COL_3 = 'KLM'
--          ID COL_1 COL_2 COL_3
--  ---------- ----- ----- -----
--           2 ABC   XYZ   KLM   
--           5 ABC   XYZ   KLM

If you want to get the excluded rows with the explanation than maybe you should consider something similar to this:

SELECT 
    t1.*,
    CASE 
        WHEN t1.COL_1 <> 'ABC' THEN 'COL_1 not = ABC'
        WHEN t1.COL_2 <> 'XYZ' THEN 'COL_2 not = XYZ'
        WHEN t1.COL_3 <> 'KLM' THEN 'COL_3 not = KLM'
    ELSE
        'OK'
    END "STATUS"
FROM
    tbl t1
--
--          ID COL_1 COL_2 COL_3 STATUS        
--  ---------- ----- ----- ----- ---------------
--           1 ABC   X     KLM   COL_2 not = XYZ 
--           2 ABC   XYZ   KLM   OK              
--           3 A     XYZ   KLM   COL_1 not = ABC 
--           4 ABC   XYZ   K     COL_3 not = KLM 
--           5 ABC   XYZ   KLM   OK

... and if you add the where clause checking the STATUS <> 'OK' ...

SELECT 
    t1.*,
    CASE 
        WHEN t1.COL_1 <> 'ABC' THEN 'COL_1 not = ABC'
        WHEN t1.COL_2 <> 'XYZ' THEN 'COL_2 not = XYZ'
        WHEN t1.COL_3 <> 'KLM' THEN 'COL_3 not = KLM'
    ELSE
        'OK'
    END "STATUS"
FROM
    tbl t1
WHERE
    CASE 
        WHEN t1.COL_1 <> 'ABC' THEN 'COL_1 not = ABC'
        WHEN t1.COL_2 <> 'XYZ' THEN 'COL_2 not = XYZ'
        WHEN t1.COL_3 <> 'KLM' THEN 'COL_3 not = KLM'
    ELSE
        'OK'
    END <> 'OK'

... then the result would be ...

--          ID COL_1 COL_2 COL_3 STATUS        
--  ---------- ----- ----- ----- ---------------
--           1 ABC   X     KLM   COL_2 not = XYZ 
--           3 A     XYZ   KLM   COL_1 not = ABC 
--           4 ABC   XYZ   K     COL_3 not = KLM

I don't know the complexity of your actual context, but maybe you could do something like this. This is, also, a simplified answer not considering possible null issues or double/tripple differencies between the data. But you can get a picture and, maybe, find your own solution. Regards...