In this case i will show how to get rid of exists check over update sql statement and i will explain a knowledge module that impelements following kind of sql statement.
Enviroment : Oracle DB & ODI 11g
Case Study:
I have an employee_list table which contains basic information about employees and now i want to make an update over job title which also triggers update over his or her salary and region information.
EMPLOYEE_LIST:
CREATE TABLE REPORTING.EMPLOYEE_LIST
(
EMP_ID NUMBER,
NAME VARCHAR2(100 CHAR),
SALARY NUMBER,
TITLE_ID NUMBER,
LOCATION_ID NUMBER
)
TABLES USED FOR UPDATE:
CREATE TABLE REPORTING.EMPLOYEE_UPDATE
(
EMP_ID NUMBER,
SALARY NUMBER,
TITLE_ID NUMBER,
LOCATION_ID NUMBER
)
CREATE TABLE REPORTING.EMPLOYEE_TITLE
(
TITLE_ID NUMBER,
TITLE VARCHAR2(100 CHAR)
)
CREATE TABLE REPORTING.EMPLOYEE_LOCATION
(
LOCATION_ID NUMBER,
REGION VARCHAR2(100 CHAR),
CITY VARCHAR2(100 CHAR)
)
Normally sql statement should be like this:
UPDATE REPORTING.EMPLOYEE_LIST
SET( SALARY , TITLE_ID , LOCATION_ID )=
(
select EMPLOYEE_LIST.SALARY + EMPLOYEE_UPDATE.SALARY AS SALARY_1 ,
EMPLOYEE_UPDATE.TITLE_ID AS TITLE_ID_1 , EMPLOYEE_UPDATE.LOCATION_ID AS LOCATION_ID_1
from REPORTING.EMPLOYEE_LIST EMPLOYEE_LIST, REPORTING.EMPLOYEE_LOCATION EMPLOYEE_LOCATION,
REPORTING.EMPLOYEE_TITLE EMPLOYEE_TITLE, REPORTING.EMPLOYEE_UPDATE EMPLOYEE_UPDATE
where 1=1 And (EMPLOYEE_LIST.EMP_ID=EMPLOYEE_UPDATE.EMP_ID)
AND (EMPLOYEE_LIST.TITLE_ID=EMPLOYEE_TITLE.TITLE_ID) AND (EMPLOYEE_LIST.LOCATION_ID=EMPLOYEE_LOCATION.LOCATION_ID)
)
WHERE EMPLOYEE_LIST.EMP_ID IN
(
select EMPLOYEE_UPDATE.EMP_ID
from REPORTING.EMPLOYEE_LIST EMPLOYEE_LIST, REPORTING.EMPLOYEE_LOCATION EMPLOYEE_LOCATION,
REPORTING.EMPLOYEE_TITLE EMPLOYEE_TITLE, REPORTING.EMPLOYEE_UPDATE EMPLOYEE_UPDATE
where 1=1 And (EMPLOYEE_LIST.EMP_ID=EMPLOYEE_UPDATE.EMP_ID)
AND (EMPLOYEE_LIST.TITLE_ID=EMPLOYEE_TITLE.TITLE_ID) AND (EMPLOYEE_LIST.LOCATION_ID=EMPLOYEE_LOCATION.LOCATION_ID)
)
This statement can work but we have to read each table twice so it is not costly efficient approach.
Instead i did like this:
UPDATE
(
select EMPLOYEE_LIST.SALARY ,EMPLOYEE_LIST.TITLE_ID ,EMPLOYEE_LIST.LOCATION_ID , EMPLOYEE_LIST.SALARY + EMPLOYEE_UPDATE.SALARY AS SALARY_1 ,
EMPLOYEE_UPDATE.TITLE_ID AS TITLE_ID_1 , EMPLOYEE_UPDATE.LOCATION_ID AS LOCATION_ID_1
from REPORTING.EMPLOYEE_LIST EMPLOYEE_LIST, REPORTING.EMPLOYEE_LOCATION EMPLOYEE_LOCATION,
REPORTING.EMPLOYEE_TITLE EMPLOYEE_TITLE, REPORTING.EMPLOYEE_UPDATE EMPLOYEE_UPDATE
where 1=1 And (EMPLOYEE_LIST.EMP_ID=EMPLOYEE_UPDATE.EMP_ID)
AND (EMPLOYEE_LIST.TITLE_ID=EMPLOYEE_TITLE.TITLE_ID) AND (EMPLOYEE_LIST.LOCATION_ID=EMPLOYEE_LOCATION.LOCATION_ID)
) TRG
SET TRG.SALARY = SALARY_1, TRG.TITLE_ID = TITLE_ID_1,
TRG.LOCATION_ID = LOCATION_ID_1
------------------------------------------------------------------------------------------------
Like this no need to EXISTS or IN clause
Note: In this approach our lookup tables has to have PRIMARY_KEYS which are
TITLE_ID FOR EMPLOYEE_TITLE
LOCATION_ID FOR EMPLOYEE_LOCATION
EMP_ID FOR EMPLOYEE_UPDATE
Otherwise we will face non-preserved key error.
-------------------------------------------------------------------------------
Now I am gonna Implement this update statement over ODI , at first i am creating an interface like a normal select - Insert script.( i am createing inner select statement which i specified above )
( Target table is used in both source and target part of the mapping)
In target mapping for the Columns , which do not be used in statement , update and insert flags has to be set to uncheck. In my example EMP_ID and NAME should be look like this:
For the ones ( SALARY , TITLE_ID , LOCATION_ID) which will be used in update statement look like this: ( update flag is checked )
and i choose the KM i wrote
For detail you can download the KM from the link below.
After Execution view of EMP_LIST table as follows:
Hiç yorum yok:
Yorum Gönder