asked on

capture changed records based upon time

Hi,
How can i do this i want to do change capture based upon LAST_UPDT and SIBEL_UPDT

Day 1

ROWID LAST_UPD SIBEL_UPD FST_NAME LAST_NAME EMAIL
123 Mar 11,2011 john sam jon@yahoo.com
456 Mar 11,2011 kirk sam kirk@yahoo.com

Day 2

ROWID LAST_UPD SIBEL_UPDT FST_NAME LAST_NAME EMAIL
123 Mar 11,2011 Sep 22,2012 john sam jon@hotmail.com
456 Mar 11,2011 kirk sam kirk@yahoo.
777 sep 22,2012 mike yang milke@yahoo.com

so first Day 1 we will insert all the records on day 2 we just want to select the latest records based upon LAST_UPD AND SIBEL_UPDT
so second run should select only 123 and 777 records.

i want to modify below query

SELECT CURRENT TIMESTAMP AS EXTR_TS, CONT.ROWID_CONT AS AA_ROWID,
'CRMAM' AS CRM_INST, CONT.CREATED, CONT.LAST_UPD,CA.LAST_UPD as sibel_upd, CONT.FST_NAME, CONT.LAST_NAME,
CONT.EMAIL_ADDR, CONT.WORK_PH_NUM, CONT.CONSUMER_FLG, CONT.ROWID_ACCT AS SIEBEL_ACCT_ROWID,
from aa.AA_CONTACT CONT
left outer join aa.SBL_CONT_ADDR CA
on CONT.ROWID_CONT = CA.ROWID_CONT
----and CONT.LAST_UPD >= ‘start of last update’ or SIBEL_UPD >= ‘sibel last update’ WITH UR;(logic goes here)

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sam2929

ASKER

getting below error

with a as (select max(MKTPERS_LASTUPD_ON) maxdt from
MKTAFF01.DIMMKTPERS)
SELECT distinct
a.MKTPERS_CRTD_ON as PDP_CRTD_ON,
'-10' as PDP_CRTD_BY,
a.MKTPERS_LASTUPD_ON as PDP_LASTUPD_ON,
'-10' as PDP_LASTUPD_BY,
DATE(a.MKTPERS_CRTD_ON) as PDP_EFFDT,
DATE(a.MKTPERS_LASTUPD_ON) AS PDP_EXPDT,
a.MKTPERSID as PDP_MKTPERSID,
b.MSTRPERSDETLID AS PDP_MKTPERSDETLID
FROM a,MKTAFF01.DIMMKTPERS a
inner join mktaff01.DIMMKTPERSDETL b
on a.MKTPERS_DERIVED_PERS_BUSKEY = b.PERSDETL_DERIVED_PERS_BUSKEY
and a.MKTPERS_ACTIVE_INDC ='Y'
and a.MKTPERS_LASTUPD_ON >= a.maxdt with ur;
------------------------------------------------------------------------------
with a as (select max(MKTPERS_LASTUPD_ON) maxdt from MKTAFF01.DIMMKTPERS) SELECT distinct a.MKTPERS_CRTD_ON as PDP_CRTD_ON, '-10' as PDP_CRTD_BY, a.MKTPERS_LASTUPD_ON as PDP_LASTUPD_ON, '-10' as PDP_LASTUPD_BY, DATE(a.MKTPERS_CRTD_ON) as PDP_EFFDT, DATE(a.MKTPERS_LASTUPD_ON) AS PDP_EXPDT, a.MKTPERSID as PDP_MKTPERSID, b.MSTRPERSDETLID AS PDP_MKTPERSDETLID FROM a,MKTAFF01.DIMMKTPERS a inner join mktaff01.DIMMKTPERSDETL b on a.MKTPERS_DERIVED_PERS_BUSKEY = b.PERSDETL_DERIVED_PERS_BUSKEY and a.MKTPERS_ACTIVE_INDC ='Y' and a.MKTPERS_LASTUPD_ON >= a.maxdt with ur
SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement
is not valid. SQLSTATE=42972