Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have a daily scheduled data extractor to extract data from oracle database with incremental script ( below script), when we cross verified the data after some point of time let’s say 1 month we found that some data is missing it’s not up to date. please check the below script and advise...
// Loading AR Customer Sites
SET vQvdFile='$(vFolderSourceData)CustomerMaster.qvd';
LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);
Set vToday = date(Today(),'DD-MMM-YYYY hh:mi:ss');
Trace >>> Running Customr Sites Scripts;
Trace >>> Running Script at $(vToday);
IF $(vQvdExists) THEN
maxdateTab:
LOAD max(Last_Update_Date) as maxdate
FROM $(vQvdFile) (qvd);
LET vIncrementalExpression = 'WHERE LAST_UPDATE_DATE >= ' & chr(39) & date(peek('maxdate'),'DD-MMM-YYYY') &chr(39) ;
DROP table maxdateTab;
ELSE
LET vIncrementalExpression = '';
END IF
TRACE >>> SQL Query Where Clause : $(vIncrementalExpression);
[AR_CustomerSites] :
Load Distinct
COMPANY as Company,
CUSTOMER_ID as Customer_Id,
CUSTOMER_NUMBER as Customer_Number,
CUSTOMER_NAME as Customer_Name,
PARTY_ID as Party_Id,
PARTY_SITE_ID as Party_Site_Id,
CREATION_DATE as Creation_Date,
CREATED_BY as Created_By,
CUSTOMER_CATEGORY_CODE as Customer_Category_Code,
TRADE_LICENCE_NO as Trade_License_No,
TRADE_LIC_EXP_DATE as Trade_License_Expiry_Date,
SALESREP_ID as Salesrep_Id,
ADDRESS1 as Address1,
ADDRESS2 as Address2,
ADDRESS3 as Address3,
ADDRESS4 as Address4,
CITY as City,
COUNTRY_CODE as Country_Code,
PO_BOX_NUMBER as PO_Box,
SITE_NAME as Site_Name,
SITE_CODE as Site_Code,
SITE_USE_CODE as Site_Use_Code,
SITE_USE_ID as Site_Use_Id,
LOCATION_ID as Location_Id,
LOCATION_CODE as Location_Code,
ORG_ID as Org_Id,
ORIG_SYSTEM_REFERENCE as Org_System_Reference,
CUST_SITE_STATUS as Cust_Site_Status,
CREDIT_LIMIT as Credit_Limit,
TERM_ID as Payment_Term_Id,
PARTY_SITE_NAME as Pasty_Site_Name,
CREDIT_HOLD as Credit_Hold,
CREDIT_CHECK as Credit_Check,
COLLECTOR as Collector,
GL_ID_REC as Code_Combination_Id,
REC_ACCOUNT as GL_Account,
PHONE as Phone,
FAX as Fax,
CONTACT_PERSON as Contact_Person,
CONTACT_PERSON_PHONE as Contact_Person_Phone,
LAST_UPDATE_DATE as Last_Update_Date,
CREDIT_HOLD_STATUS as Credit_Hold_Status ;
SQL select
COMPANY,
CUSTOMER_ID,
CUSTOMER_NUMBER,
CUSTOMER_NAME,
PARTY_ID,
PARTY_SITE_ID,
CREATION_DATE,
CREATED_BY,
CUSTOMER_CATEGORY_CODE,
TRADE_LICENCE_NO,
TRADE_LIC_EXP_DATE,
SALESREP_ID,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
CITY,
COUNTRY_CODE,
PO_BOX_NUMBER,
SITE_NAME,
SITE_CODE,
SITE_USE_CODE,
SITE_USE_ID,
LOCATION_ID,
LOCATION_CODE,
ORG_ID,
ORIG_SYSTEM_REFERENCE,
CUST_SITE_STATUS,
CREDIT_LIMIT,
TERM_ID,
PARTY_SITE_NAME,
CREDIT_HOLD,
CREDIT_CHECK,
COLLECTOR,
GL_ID_REC,
REC_ACCOUNT,
PHONE,
FAX,
CONTACT_PERSON,
CONTACT_PERSON_PHONE,
LAST_UPDATE_DATE,
CREDIT_HOLD_STATUS
from APPS.XXBI_CUST_SITE_DETAILS $(vIncrementalExpression) ;
LET vNumRows = NoOfRows('AR_CustomerSites');
TRACE >>> Table --> AR_CustomerSites New records loaded with : $(vNumRows);
IF $(vQvdExists) THEN
CONCATENATE (AR_CustomerSites)
LOAD * FROM $(vQvdFile) (qvd)
WHERE NOT exists(Party_Site_Id);
END IF
STORE [AR_CustomerSites] into $(vQvdFile) (qvd);
LET vNumRows = NoOfRows('AR_CustomerSites');
TRACE >>> Table --> AR_CustomerSites Table loaded with : $(vNumRows);
Drop Table [AR_CustomerSites];
rergards
Madhu
Madhu G,
From your script I would assume that the data gets updated daily and is unique by day, meaning that once the database is updated for a given day, the data for that day is never updated. Is that the case?
I ask because we saw issues with this type of date-driven incremental loading because the database was updating older records (than the ones that were updated since our last reload). Records were being modified, reinstated, or removed.
It looks ok because you are testing LAST_UPDATE_DATE which I assume gets updated when an older record gets updated.
Is PARTY_SITE_ID a true primary key? That is, is it unique for every record?
-Rob