?
Solved

capture changed records based upon time

Posted on 2012-09-22
2
Medium Priority
?
351 Views
Last Modified: 2012-10-28
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)
0
Comment
Question by:sam2929
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 38425268
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
 

Author Comment

by:sam2929
ID: 38543132
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question