Hi community...
I need to resolve this very quickly.
I established an extended table for CONTACT_HISTORY table of Customer Intelligence Common Data Model. I want to update a variable, this is LAST_CONTACT_DT.
HERE IS THE EXTENDED TABLE:
CELL_PACKAGE_SK NUMBER(10) NOT NULL ,
CAMPAIGN_CD NVARCHAR2(30) NOT NULL,
CUSTOMER_ID NUMBER(10) NOT NULL ,
LAST_CONTACT_DT DATE NOT NULL,
CICLO_ID NVARCHAR2(2) NOT NULL,
END_MANAGEMENT_DT DATE NOT NULL
For each occurrence for every communication for some campaign, if the Customer exist, i need to update the value of the variable.
First, I try this...
PROC SQL;
UPDATE LIB.CI_CONTACT_HISTORY_C_EXTENDED SET LAST_CONTACT_DT = &FECHA. WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM EXIST);
QUIT;
That code, update the value for each Customer in the table, but for each campaign (CAMPAIGN_CD) that the customer exist. I only need to update the variable which contain the same END_MANAGEMENT_DT f the campaign that is being processed .. so.. I posed the following:
PROC SQL;
UPDATE LIB.CI_CONTACT_HISTORY_C_EXTENDED SET LAST_CONTACT_DT = &FECHA.
FROM LIB.CI_CONTACT_HISTORY_C_EXTENDED
INNER JOIN WORK.EXIST EXISTS
ON EXTENDED.CUSTOMER_ID = EXISTS.CUSTOMER_ID AND
DATEPART(EXTENDED.END_MANAGEMENT_DT) = EXISTS.CONTACT_DT;
QUIT; /*The datepart function is required. WORK is SAS Code and the extended table is ORACLE Schema.*/
But this, doesn´t work.
Has anybody knows how to do that? How to realice an update from inner join between this tables: which contains the Customers in the occurrence of the communication and the table which contains that customers for the last occurrence of the previous communication.
