ORACLE, SELECT * FROM 2 TABLES WHERE CRITERIA DOES NOT MATCH

Question

We have a batch process that runs periodically and validates shipment data, then dumps rows pertaining to errors onto a table called: AN_VALIDATION_ERRORS table.

The first time it runs, i could get data that looks like this(ill do an example with an error at every level):

error_seq | asn | po   | carton | upc  | error_code | error_origin | error_level
--------------------------------------------------------------------
  1       | 1   | null | null   | null | SO         | B            | S -- shipment
  2       | 1   | 90   | null   | null | NF         | B            | O -- order
  3       | 1   | 92   | 45     | null | SC         | B            | C -- carton
  4       | 1   | 92   | 45     | 567  | ST         | B            | I -- item

When the data is re-validated, the batch process just dumps more rows onto this table, but doesn't clean up old rows. However with one difference, the subsequent times, it dumps ones with error_origin = 'S'. The data would now look like this:

error_seq | asn | po   | carton | upc  | error_code | error_origin | error_level
--------------------------------------------------------------------
  1       | 1   | null | null   | null | SO         | B            | S -- shipment
  2       | 1   | 90   | null   | null | NF         | B            | O -- order
  3       | 1   | 92   | 45     | null | SC         | B            | C -- carton
  4       | 1   | 92   | 45     | 567  | ST         | B            | I -- item

  5       | 1   | 92   | 45     | null | SC         | S            | C -- new row

What this means, is all the rows that were not re-created, were cleared, and the errors that persist are the ones with an error_origin of 'S'. In my example, error_seq 3 and 5, are the same error, and the rest are cleared, or fixed.

It is easy for me to get a list of the current errors by selecting where error_origin = 'S' in the case that a row has an 'S'. However, i need to also be able to get a list of the errors that were previously fixed. In my example that would be rows of error_seq {1,2,4}.

This is what i have tried, which is wrong, but as an idea of what im trying todo:

WITH B_LIST AS (

 SELECT *
 FROM ASN.AN_VALIDATION_ERRORS
 WHERE ERROR_ORIGIN = 'B'   
 AND INTERNAL_ASN = 1

)

, S_LIST AS (

-- ALL OPEN ERRORS
 SELECT *
 FROM ASN.AN_VALIDATION_ERRORS
 WHERE ERROR_ORIGIN = 'S'  
 AND INTERNAL_ASN = 1

)
, CLOSED_LIST AS (

 -- TRY TO GET ALL ROWS WITH ERROR_ORIGIN = 'B' WHERE A CORRESPONDING ROW OF ERROR_ORIGIN = 'S' DOES NOT EXIST
 SELECT *
 FROM B_LIST BL
 WHERE NOT EXISTS (
  SELECT * 
  FROM S_LIST SL
  WHERE SL.ORIG_PO_NO = BL.PO_NO
  AND SL.CARTON_NO = BL.CARTON_NO
  AND SL.UPC_NO = BL.UPC_NO
  AND SL.ERROR_CODE = BL.ERROR_CODE
 )
)

SELECT * FROM CLOSED_LIST;

This query needs to give me a list off all errors that have been fixed, and using my data as an example, the result needs to be this:

error_seq | asn | po   | carton | upc  | error_code | error_origin | error_level
--------------------------------------------------------------------
  1       | 1   | null | null   | null | SO         | B            | S -- shipment
  2       | 1   | 90   | null   | null | NF         | B            | O -- order
  4       | 1   | 92   | 45     | 567  | ST         | B            | I -- item

Answer 1

Previously fixed errors are those where there is no more recent row with an "S". I am a bit unclear on what the real matching criteria are between the rows, so I am guessing that it is asn and error_code.

You can get what you want using not exists, something like this:

select cl.*
from ASN.AN_VALIDATION_ERRORS ve
where error_origin = 'B' and
      not exists (select 1
                  from ASN.AN_VALIDATION_ERRORS ve2
                  where ve2.asn = ve.asn and
                        ve2.error_code = ve.error_code and
                        ve2.id > ve.id and
                        ve2.error_origin = 'S'
                 );

EDIT:

For the full list of matching columns (which can also be NULL):

select cl.*
from ASN.AN_VALIDATION_ERRORS ve
where error_origin = 'B' and
      not exists (select 1
                  from ASN.AN_VALIDATION_ERRORS ve2
                  where ve2.asn = ve.asn and
                        ve2.error_code = ve.error_code and
                        (ve2.po = ve.po or ve2.po is null and ve.po is null) and
                        (ve2.carton = ve.carton or ve2.carton is null and ve.carton is null) and
                        (ve2.upc = ve.upc or ve2.upc is null and ve.upc is null) and
                        ve2.id > ve.id and
                        ve2.error_origin = 'S'
                 );