The following example shows required modifications to the aci_runtab_upgrd SQL upgrade script for supporting additional audience levels.
Your existing Interact design environment contains an additional audience level called Household. To support this audience level, your
Interact runtime environment database contains tables named
HH_CHStaging and
HH_RHStaging.
You must also revise the SQL to support the data type change for the SeqNum column in the
UACI_RHStaging table. The value of the
SeqNum is a sequential number across all response history staging tables. The next value used is tracked by the
NextID column in the
UACI_IdsByType table where the
TypeID is
2. For example, you have three audience levels, customer, household, and account. In the customer response history staging table the highest
SeqNum is
50. In the household response history staging table the highest
SeqNum is
75. In the account response history staging table the highest
SeqNum is
100. Therefore, you must alter the SQL to set the
NextID for
TypeID = 2 in the
UACI_IdsByType to
101.
The following example SQL statements show the required additions to the aci_runtab_upgrd_sqlsvr.sql script for a SQL Server database containing the Household audience level. Text that has been changed to support the Household audience level is in bold:
ALTER TABLE UACI_CHStaging ADD RTSelectionMethod int NULL
go
ALTER TABLE UACI_RHStaging ADD RTSelectionMethod int NULL
go
ALTER TABLE HH_CHStaging ADD RTSelectionMethod int NULL
go
ALTER TABLE HH_RHStaging ADD RTSelectionMethod int NULL
goinsert into UACI_IdsByType (TypeID, NextID) (select 2,
IDENT_CURRENT('UACI_RHStaging')
+ IDENT_CURRENT('HH_RHStaging') + IDENT_INCR( 'UACI_RHStaging' ))
go
select * into UACI_RHStaging_COPY from UACI_RHStaging
go
select * into HH_RHStaging_COPY from HH_RHStaging
goDROP TABLE UACI_RHStaging
go
CREATE TABLE UACI_RHStaging (
SeqNum bigint NOT NULL,
TreatmentCode varchar(512) NULL,
CustomerID bigint NULL,
ResponseDate datetime NULL,
ResponseType int NULL,
ResponseTypeCode varchar(64) NULL,
Mark bigint NOT NULL
DEFAULT 0,
UserDefinedFields char(18) NULL,
RTSelectionMethod int NULL,
CONSTRAINT iRHStaging_PK
PRIMARY KEY (SeqNum ASC)
)
go
insert into UACI_RHStaging (SeqNum, TreatmentCode, CustomerID, ResponseDate,
ResponseType, ResponseTypeCode, Mark, UserDefinedFields, RTSelectionMethod)
(select SeqNum, TreatmentCode, CustomerID, ResponseDate, ResponseType,
ResponseTypeCode, Mark, UserDefinedFields, RTSelectionMethod from
UACI_RHStaging_COPY)
go
DROP TABLE UACI_RHStaging_COPY
go
DROP TABLE HH_RHStaging
go
CREATE TABLE HH_RHStaging (
SeqNum bigint NOT NULL,
TreatmentCode varchar(512) NULL,
HouseholdID bigint NULL,
ResponseDate datetime NULL,
ResponseType int NULL,
ResponseTypeCode varchar(64) NULL,
Mark bigint NOT NULL
DEFAULT 0,
UserDefinedFields char(18) NULL,
RTSelectionMethod int NULL,
CONSTRAINT iRHStaging_PK
PRIMARY KEY (SeqNum ASC)
)
go
insert into HH_RHStaging (SeqNum, TreatmentCode, HouseHoldID, ResponseDate,
ResponseType, ResponseTypeCode, Mark, UserDefinedFields, RTSelectionMethod)
(select SeqNum, TreatmentCode, HouseHoldID, ResponseDate, ResponseType,
ResponseTypeCode, Mark, UserDefinedFields, RTSelectionMethod from
HH_RHStaging_COPY)
go
DROP TABLE HH_RHStaging_COPY
go
For DB2® and Oracle databases, the following statement would be used for inserting values into the
UACI_IdsByType table:
If you have multiple audiences, you will need to add sections to the aci_usrtab_upgrd SQL script for each audience level as shown in the following example. The example shows the modified additions only.