DELTA

[bobby]

HI ,
I AM SENDING ONE SAMPLE FRP INCREMENT LAOD IS IT OK
PL. GUIDE

SELECT ATS_ACTIVATION.ACCT_NO,
ATS_ACTIVATION.CONVERTER_SERIAL_NO,
ATS_ACTIVATION.RECEIVER_TYPE_ID,
ATS_ACTIVATION.ACTIVATION_TYPE_ID,
TO_CHAR(ATS_ACTIVATION.ACTIVATION_DTE,'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(ATS_ACTIVATION.CREATED_DTE,'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(ATS_ACTIVATION.LASTMODIFIED_DTE,'YYYY-MM-DD HH24:MI:SS'),
ATS_ACTIVATION.ETL_CONTROL_KEY,
TO_CHAR(SUBSCRIBER.CREATION_DATE, 'YYYY-MM-DD HH24:MI:SS')
FROM #$SOURCE_SCHEMA#.ATS_ACTIVATION ATS_ACTIVATION,
#$SCHEMA_CCS#.SUBSCRIBER SUBSCRIBER
WHERE ATS_ACTIVATION.ACCT_NO = SUBSCRIBER.ACCT_NO (+) AND
TO_CHAR(ATS_ACTIVATION.CREATED_DTE,'YYYY-MM-DD HH24:MI:SS') >
(SELECT MAX(TO_CHAR(ETL_CONTROL_TABLE_LOAD.MAX_EXTRACT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS'))
FROM #$CONTROL_SCHEMA#.ETL_CONTROL_TABLE_LOAD
WHERE ETL_CONTROL_TABLE_LOAD.TABLE_NAME = 'STG_ATS_ACTIVATION')

[vmcburney]

I can see minor change. There is no need to reformat your dates in the WHERE clause:

Code: Select all

TO_CHAR(ATS_ACTIVATION.CREATED_DTE,'YYYY-MM-DD HH24:MI:SS') > 
(SELECT MAX(TO_CHAR(ETL_CONTROL_TABLE_LOAD.MAX_EXTRACT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')) 

should be

Code: Select all

ATS_ACTIVATION.CREATED_DTE > MAX(ETL_CONTROL_TABLE_LOAD.MAX_EXTRACT_TIMESTAMP) 

You could further simplify this SQL, and the SQL of all your other Delta jobs, by retrieving the MAX_EXTRACT_TIMESTAMP from the ETL table in a sequence job via a routine and then pass this date into all your delta jobs as a job parameter. It is easier to maintain a single delta date per batch load instead of a date per load for each table. The delta data you move across is synchronised to the same date which would reduce the risk of referential integrity problems.

[bobby]

hI,
i have adress_id in source and mapped to staging adress_id ,now if u want to increment to adress_id (its unique ) how can i do it, if any example like compare can be given it will be help.
Thanks,
Bobby