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)
sam2929Asked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
with a as (select max(case when last_upd > sibel_updt then last_upd else sibel_updt end) maxdt from
 SBL_CONT_ADDR) 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 a, aa.AA_CONTACT CONT
left outer join aa.SBL_CONT_ADDR CA
on CONT.ROWID_CONT = CA.ROWID_CONT
and CONT.LAST_UPD >= a.maxdt or SIBEL_UPD >= a.maxdt WITH UR;
0
 
sam2929Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.