Our requirement is like this:
For the fields CUST_ID , CURRENT_ACCOUNT_NUMBER from the source table CUST_ACCT_DIM ,nonmon information will be captured from different sources and kept in the table CUST_C360_DIM
Layout of CUST_ACCT_DIM
******************************
CUST_ACCT_DIM_KEY
CUST_ID
CURRENT_ACCOUNT_NBR
EFFECTIVE_END_DATE(date on which the record gets updated
(for a particular customer id if effective end date is null then it is considered to be the latest information(correct information))
LOAD_DATE(date on which the record gets inserted)
CLIENT_ID
Layout of CUST_C360_DIM
*************************
CUST_ID
CURRENT_ACCOUNT_NBR
NEW_ACCT_FLAG
LOAD_DATE
INSERT_DATE
CUSTOMER_ROLE
MEMBER_SEQUENCE_NUMBER
NONMON_NUMBER
NONMON_SUB_TRAN_CODE
For onetime load:Along with the nonmon attributes we load the table CUST_C360_DIM taking the columns CUST_ID and CURRENT_ACCOUNT_NBR from CUST_ACCT_DIM where EFFECTIVE_END_DATE is NULL. At the same time LOAD_DATE, INSERT_DATE and NEW_ACCT_FLAG columns will be populated with the default values SYSDATE,SYSDATE and Y respectively.
For go foreward load:We take the cust_id and Current_account_nbr from CUST_ACCT_DIM with latest LOAD_DATE and compare with the cust_id and Current_account_nbr s existing in the CUST_C360_DIM and based on that we do the updating or insertion.
I have to create one job both for onetime and go foreward load.I am using ODBC satge as source for table CUST_ACCT_DIM.I am doing a look up on CUST_C360_DIM.For one time loading its not a problem as CUST_C360_DIM will be empty and load everyting in CUST_C360_DIM as per the logic given above.But my problem is to achive the go foreward load logic within the same job.How can i use the same look up to compare the the latest records with the custid and accountnumberbr each week as this loading will be on weekly basis.
ODBC------------------------------------ODBC(insert)
|
|look up
ODBC..............................................ODBC(update)
Please help me to achieve the two functionalities in a single job.If you will provide any sample job structure it will be very helpful.
one time nad go foreward loading in a single job
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: