Hello everyone ,
I am trying to load data to the satellite table in the Data vault using Mapping. The table structure is as follows:
I am using HASH_DIFF to insert when there is an update in the source, and I want to do insert only when there is new data coming from the source.
To do that I created a Mapping, and it works well for the insert for the first time and when I execute it for the second time data is also inserted because HUB_COUNTRY_KEY & SAT_LOAD_DTS are primary keys and SAT_LOAD_DTS is taking sysdate .
This is the SQL that Mapping is generated :
MERGE
INTO DATAVAULTTARGET.SAT_COUNTRIES SAT_COUNTRIES
USING
(
SELECT
(DBMS_OBFUSCATION_TOOLKIT.md5(input => UTL_I18N.STRING_TO_RAW (COUNTRIES.COUNTRY_ID))) AS HUB_COUNTRY_KEY ,
sysdate AS SAT_LOAD_DTS ,
(DBMS_OBFUSCATION_TOOLKIT.md5(input => UTL_I18N.STRING_TO_RAW (COUNTRIES.COUNTRY_NAME))) AS HASH_DIFF ,
'HR_TAB' AS SAT_REC_SRC ,
COUNTRIES.COUNTRY_NAME AS COUNTRY_NAME
FROM
HR.COUNTRIES@DB_HR COUNTRIES
) MERGE_SUBQUERY
ON
(
SAT_COUNTRIES.HUB_COUNTRY_KEY = MERGE_SUBQUERY.HUB_COUNTRY_KEY AND SAT_COUNTRIES.SAT_LOAD_DTS = MERGE_SUBQUERY.SAT_LOAD_DTS
)
WHEN NOT MATCHED THEN
INSERT
(
HUB_COUNTRY_KEY ,
SAT_LOAD_DTS ,
HASH_DIFF ,
SAT_REC_SRC ,
COUNTRY_NAME
)
VALUES
(
MERGE_SUBQUERY.HUB_COUNTRY_KEY ,
MERGE_SUBQUERY.SAT_LOAD_DTS ,
MERGE_SUBQUERY.HASH_DIFF ,
MERGE_SUBQUERY.SAT_REC_SRC ,
MERGE_SUBQUERY.COUNTRY_NAME
)
WHEN MATCHED THEN
UPDATE SET
HASH_DIFF = MERGE_SUBQUERY.HASH_DIFF ,
SAT_REC_SRC = MERGE_SUBQUERY.SAT_REC_SRC ,
COUNTRY_NAME = MERGE_SUBQUERY.COUNTRY_NAME
To do insert with a duplicate I have to ignore this condition
AND SAT_COUNTRIES.SAT_LOAD_DTS = MERGE_SUBQUERY.SAT_LOAD_DTS , but ODI is generating the condition for the primary key as it consists of two fields.
How can I let ODI insert only new, and when there is a change in Hash_diff just to insert the it as new .
Can any one help ?
Hash_diff must be compared with coming data for COUNTRY_NAME.