How to resolve the error generated while adding a new user defined field to the "Task In Progress" table in IBM Case Analyzer

Troubleshooting

Problem

If a Case Analyzer store is upgraded to 5.5.x, the user might not be able to add new user-defined fields to the Task In Progress table. This issue does not occur with new 5.5.x Case Analyzer installations.

Symptom

The following type of error occurs when attempting to add a new user-defined field to the Task In Progress table:

[Error] [CaseAnalyzer_Store] UDFAdmin::nearTermViewUpdate:F_DMTaskWIP - Column names in each view or function must be unique. Column name 'VMAE_CustomerName_key' in view or function 'V_F_DMTaskWIP' is specified more than once. ; Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Column names in each view or function must be unique. Column name 'VMAE_CustomerName_key' in view or function 'V_F_DMTaskWIP' is specified more than once.

Environment

The issue can occur if both of the following statements are true:

The Case Analyzer store is upgraded to 5.5.x. The error does not occur with Case Analyzer stores created on 5.5.x.
The Case Analyzer store is using a DB2 or SQL Server database. This issue is not applicable for Case Analyzer stores on Oracle databases.

Resolving The Problem

Use the following steps to work around the issue:

Stop the Case Analyzer server.
Backup the Case Analyzer database.
Create a copy of the view definition V_F_DMTaskWIP.
Drop the V_F_DMTaskWIP view.
Create a new view called V_F_DMTaskWIP by replacing the '*' in the view definition saved in step 3 with all the individual columns from the F_DMTaskWIP table along with any user-defined fields.

Examples

If using a Microsoft SQL Server database with Case Analyzer, use the following statement to create the new view:

CREATE VIEW [dbo].[V_F_DMTaskWIP] AS
        SELECT DMTask_key,
            DMCase_key,
            DMTaskState_key,
            EventType,
            DMUser_key,
            DMTaskType_key,
            TSLastEvent,
            SeqNumber,
            WaitCurrentMinutes,
            ReadyCurrentMinutes,
            ProcCurrentMinutes,
            FailCurrentMinutes,
            IsInWaitStatus,
            IsInReadyStatus,
            IsInProcStatus,
            IsInFailStatus,
            Attributes,
            HasBeenWorking,
            MinutesSinceLastEvent,
            RestartCount,
            <User Defined Field1 from F_DMTaskWIP table>,
            <User Defined Field2 from F_DMTaskWIP table>,
            <User Defined Field3 from F_DMTaskWIP table>
        FROM dbo.F_DMTaskWIP Where DMTaskState_key!=0 and DMTaskState_key!=1 and DMTaskState_key!=3

If using a DB2 database with Case Analyzer, use the following statement to create the new view:

CREATE VIEW "CA"."V_F_DMTASKWIP" AS
SELECT DMTask_key,
            DMCase_key,
            DMTaskState_key,
            EventType,
            DMUser_key,
            DMTaskType_key,
            TSLastEvent,
            SeqNumber,
            WaitCurrentMinutes,
            ReadyCurrentMinutes,
            ProcCurrentMinutes,
            FailCurrentMinutes,
            IsInWaitStatus,
            IsInReadyStatus,
            IsInProcStatus,
            IsInFailStatus,
            Attributes,
            HasBeenWorking,
            timestampdiff(4, char(current timestamp - TSLastEvent)) as MinutesSinceLastEvent,
            RestartCount,

            <User Defined Field1 from F_DMTaskWIP table>,
            <User Defined Field2 from F_DMTaskWIP table>,
            <User Defined Field3 from F_DMTaskWIP table>
        FROM CA.F_DMTaskWIP WHERE DMTaskState_key!=3 AND DMTaskState_key!=1 AND DMTaskState_key!=0;

Document Location

Worldwide

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSTHRT","label":"IBM Case Foundation"},"Component":"Case Analyzer","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"5.5.x","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Was this topic helpful?

Document Information

More support for:
IBM Case Foundation

Software version:
5.5.x

Document number:
1104207

Modified date:
15 November 2019

UID

ibm11104207

IBM Support

Tips