Detection Strategies
Detection Strategies:
A. NOT
EXISTS
B. MINUS
C. POST_FLOW
D. NONE
A. NOT
EXISTS
a.
Load data from source table to C$
b.
Compare to C$ vs Target table with all fields
and inserts into I$ if it is not matching with IND_UPDATE flag as ‘I’
c.
Identify update records based on Unique Key
field and update the IND_UPDATE flag as ‘U’
d.
Whose flags are ‘U’ those records are updated to
target table
e.
Whose flags are ‘I’ those records are inserted
to target table
Sample code for step b. as
follows:
|
/* DETECTION_STRATEGY = NOT_EXISTS */
insert into ODI_TEMP.I$_DEPT
(
DEPARTMENT_ID,
MANAGER_ID,
DEPARTMENT_NAME,
IND_UPDATE
)
select
DEPARTMENT_ID,
MANAGER_ID,
DEPARTMENT_NAME,
IND_UPDATE
from (
select
DEPARTMENTS.DEPARTMENT_ID
DEPARTMENT_ID,
DEPARTMENTS.MANAGER_ID
MANAGER_ID,
DEPARTMENTS.DEPARTMENT_NAME
DEPARTMENT_NAME,
'I'
IND_UPDATE
from REPLICA.DEPARTMENTS
DEPARTMENTS
where (1=1)
) S
where NOT EXISTS
(
select 1 from REPLICA.DEPT T
where T.DEPARTMENT_ID = S.DEPARTMENT_ID
and (( T.MANAGER_ID = S.MANAGER_ID) or
(T.MANAGER_ID IS NULL and S.MANAGER_ID IS NULL)) and
((T.DEPARTMENT_NAME
= S.DEPARTMENT_NAME) or (T.DEPARTMENT_NAME IS NULL and S.DEPARTMENT_NAME IS
NULL))
)
|
B. MINUS
a.
Load data from source table to C$
b.
Compare to C$ vs Target table with minus
operator and inserts into I$ if it is not matching with IND_UPDATE flag as ‘I’
c.
Identify update records based on Unique Key
field and update the IND_UPDATE flag as ‘U’
d.
Whose flags are ‘U’ those records are updated to
target table
e.
Whose flags are ‘I’ those records are inserted
to target table
Sample code for step b. as
follows:
|
/* DETECTION_STRATEGY = MINUS */
insert into ODI_TEMP.I$_DEPT
(
DEPARTMENT_ID,
MANAGER_ID,
DEPARTMENT_NAME,
IND_UPDATE
)
select
DEPARTMENTS.DEPARTMENT_ID,
DEPARTMENTS.MANAGER_ID,
DEPARTMENTS.DEPARTMENT_NAME,
'I'
IND_UPDATE
from REPLICA.DEPARTMENTS
DEPARTMENTS
where (1=1)
minus
select
DEPARTMENT_ID,
MANAGER_ID,
DEPARTMENT_NAME,
'I' IND_UPDATE
from REPLICA.DEPT
|
C. POST_FLOW
a.
Load data from source table to C$
b.
Copy from C$ to I$ with IND_UPDATE flag as ‘I’
c.
Identify update records based on Unique Key
field and update the IND_UPDATE flag as ‘U’
d.
Identify not updated records by comparing fields
with target and update the IND_UPDATE flags as ‘N’
e.
Whose flags are ‘U’ those records are updated to
target table
f.
Whose flags are ‘I’ those records are inserted
to target table
Sample code for step d. as
follows:
|
/* DETECTION_STRATEGY = POST_FLOW */
update ODI_TEMP.I$_DEPT
S
set IND_UPDATE
= 'N'
where exists
(
select
'X'
from
REPLICA.DEPT T
where T.DEPARTMENT_ID = S.DEPARTMENT_ID
and ((T.MANAGER_ID = S.MANAGER_ID) or
(T.MANAGER_ID IS NULL and S.MANAGER_ID IS NULL))
and ((T.DEPARTMENT_NAME
= S.DEPARTMENT_NAME) or (T.DEPARTMENT_NAME IS NULL and S.DEPARTMENT_NAME IS
NULL))
)
|
D. NONE
a.
Load data from source table to C$
b.
Copy from C$ to I$ with IND_UPDATE flag as ‘I’
c.
Identify update records based on Unique Key
field and update the IND_UPDATE flag as ‘U’
d.
Whose flags are ‘U’ those records are updated to
target table
e.
Whose flags are ‘I’ those records are inserted
to target table
[Note: It will update all the records whether it got changed or not
changed]
No comments:
Post a Comment