Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

ALTER TABLE error:importing SI Framework Jar 7.11 post upgrade

SA-39568

Summary



User is upgrading Smart Investigate (SI) for Payments 7.11 version with changes in below mentioned table schema.

Hence import wizard is not allowing user to proceed further to import the jar file in to user Sand Box environment.

User has taken the SQL scripts and executed manually and found issues in below two tables.


Schema.WORKACTIONS: ( User is using this table in our appplication to store values when ever there is an operation done on work item)
In this table there is a change in two columns(RECDATE, PZINSKEY) which we cannot change.


Added not null
condition
ALTER TABLE Schema.WORKACTIONS MODIFY "RECDATE" VARCHAR2 (35) NOT NULL;

Adding RECDATE as primary keyExisting Column

ALTER TABLE GIN.WORKACTIONS ADD CONSTRAINT WORKACTIONS_PK PRIMARY KEY ("RECDATE");

Changes the column size and data type
ALTER TABLE Schema.WORKACTIONS MODIFY "PZINSKEY" VARCHAR2 (255)

Schema.INTERESTRATES: (User is not using it in their application)

In this table there is a change in two columns(ID, INTERESTRATE) which one cannot change.

Added not null condition
ALTER TABLE Schema.INTERESTRATES ADD "ID" VARCHAR2 (20) NOT NULL;

Adding ID as primary key new Column
ALTER TABLE Schema.INTERESTRATES ADD CONSTRAINT INTERESTRATES_PK PRIMARY KEY ("ID");

Changed the data type from float to numeric
ALTER TABLE Schema.INTERESTRATES MODIFY "INTERESTRATE" NUMBER (5, 3);


Error Messages



"The above SQL Statements contain a change to a column type. Automatic maintenance does not apply Column Type changes."


Steps to Reproduce



Importing SI Framework 7.11 jar in Pega 7.2.2.


Root Cause




SQL statements contains change to column type which have to executed manullay while importing the SI framework.

Resolution



Perform the following local-change steps:
1. Rename the existing tables 'WORKACTIONS' and 'INTERESTRATES' into 'WORKACTIONS_ORG' , 'INTERESTRATES_ORG'.
2. Run the import wizard, choose the Manual options for schema changes and Download the SQL script.
3. Run the generated SQL scripts, which contains create table scripts for 'WORKACTIONS' and 'INTERESTRATES' tables, since these tables were renamed.
4. Merge the tables 'INTERESTRATES' , 'INTERESTRATES_ORG' using the following SQL script.
MERGE INTO INTERESTRATES a USING (
SELECT CURRENCY, ID, TYPE, FEEDDATE, INTERESTRATE FROM
INTERESTRATES_ORG) b
ON (a.ID = b.ID)
WHEN MATCHED THEN
UPDATE SET
a.CURRENCY = b.CURRENCY,
a.TYPE = b.TYPE,
a.INTERESTRATE = b.INTERESTRATE,
a.FEEDDATE = b.FEEDDATE
WHERE a.ID = null
WHEN NOT MATCHED THEN
INSERT (a.CURRENCY, a.ID, a.TYPE, a.FEEDDATE, a.INTERESTRATE)
VALUES (b.CURRENCY, b.ID, b.TYPE, b.FEEDDATE, b.INTERESTRATE);
5. Drop the primary key constraint defined on recdate column in the 'WORKACTIONS' table.
6. Make the recdate column as NOT NULL.
7. Merge the tables 'WORKACTIONS' , 'WORKACTIONS_ORG' using the following sql script.
MERGE INTO WORKACTIONS a USING (
SELECT ACTION, RECDATE, OPERATOR, RESPONSIBLEOPERATOR, CASEID, PXOBJCLASS, PZINSKEY, WORKACTIONDATE, PXINSNAME
FROM WORKACTIONS_ORG) b
ON (a.RECDATE = b.RECDATE)
WHEN MATCHED THEN
UPDATE SET
a.ACTION = b.ACTION,
a.OPERATOR = b.OPERATOR,
a.RESPONSIBLEOPERATOR = b.RESPONSIBLEOPERATOR,
a.CASEID = b.CASEID,
a.PXOBJCLASS = b.PXOBJCLASS,
a.PZINSKEY = b.PZINSKEY,
a.WORKACTIONDATE = b.WORKACTIONDATE,
a.PXINSNAME = b.PXINSNAME
WHERE a.RECDATE = null
WHEN NOT MATCHED THEN
INSERT (a.RECDATE, a.ACTION, a.OPERATOR, a.RESPONSIBLEOPERATOR, a.CASEID, a.PXOBJCLASS, a.PZINSKEY, a.WORKACTIONDATE, a.PXINSNAME)
VALUES (b.RECDATE, b.ACTION, b.OPERATOR, b.RESPONSIBLEOPERATOR, b.CASEID, b.PXOBJCLASS, b.PZINSKEY, b.WORKACTIONDATE, b.PXINSNAME);
8. Run the import wizard, select the manual option for schema changes, and proceed further with the import.
9. Drop the renamed tables 'WORKACTIONS_ORG' , 'INTERESTRATES_ORG'.

Published December 12, 2017 - Updated December 2, 2021

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us