When loading data into a target table, it is a common requirement to reject data which does not match data in another table. 


This article shows two examples:

  • Example 1: reject detection is materialized and automatized with a Forein Key (FK) - quick and easy
  • Example 2: reject detection is based on functional/mixed conditions which cannot be designed with Foreign Keys


Example 1 : using Foreign Keys


In this example, we want to load data into T_CUSTOMER, keeping track of Rejects when TIT_CODE does not exist in the T_TITLE table. 




With this configuration, the Reject Template will load the Reject table with a SQL query such as this one: 

insert into PUBLIC.R_T_CUSTOMER /* this is the rejects table */
(...)
select ...
from PUBLIC.I_T_CUSTOMER FK_TAB
where not exists (
    select 'X'
    from PUBLIC.T_TITLE PK_TAB
    where PK_TAB.TIT_CODE=FK_TAB.TIT_CODE
)
And FK_TAB.TIT_CODE IS NOT NULL


Example 2 : using a Condition


In this mapping, we want to load data into T_PAYMENT_TYPE only if the Payment Type exists in the T_BILLING table. 



For this, we have created a Condition in the T_PAYMENT_TYPE metadata like this: 



The Condition's SQL expression is using some XPath to make sure that the Schema Name can be configured at deployment time: 

EXISTS (
SELECT * FROM %x{md:objectPath(ref:schema(), 'T_BILLING')}x% B
WHERE B.PMT_CODE = T_PAYMENT_TYPE.PMT_CODE
)



Here is the SQL generated for detecting rejects, when building the mapping: 


insert into PUBLIC.R_T_PAYMENT_TYPE 
(...)
select ...
from PUBLIC.I_T_PAYMENT_TYPE T_PAYMENT_TYPE
where not (
    EXISTS (
    SELECT * FROM PUBLIC.T_BILLING B
    WHERE B.PMT_CODE = T_PAYMENT_TYPE.PMT_CODE
))