Users are Unable to Modify Portlet Views

Products

• Clarity PPM On Premise

Issue/Introduction

At times, duplicate records can get generated in the odf_view_attributes table (exact steps are unknown), which leads to users not being able to modify the view. 

Steps to Reproduce:

1. Add an attribute to the underlying query.
   
   
2. Make changes to the Portlet filter field sections for the newly added attribute.
   
   
3. Go to Portlet Layout section, move the columns in the filter layout section from the Selected column to

Expected Results: Save to be processed.
Actual Results: Received the following error: "System error. Contact system administrator." The app-ca log contains the following:

In MSSQL: [CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot insert duplicate key row in object 'dbo.odf_view_attributes' with unique index 'ODF_VIEW_ATTRIBUTES_U3'.

In Oracle: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (SC52133P.ODF_VIEW_ATTRIBUTES_U3) violated

Resolution

Perform the following steps:

1. Back up the odf_view_attributes table.
   
   
2. Run the following script to delete the duplicates:

DELETE from odf_view_attributes
WHERE id in (SELECT attrs2.id
FROM odf_views views1,
odf_views views2,
odf_subviews sv1,
odf_subviews sv2,
odf_view_attributes attrs1,
odf_view_attributes attrs2
WHERE views1.view_type = 'filter'
AND views2.view_type = 'filter'
AND attrs1.view_id = views1.ID
AND attrs2.view_id = views2.ID
AND sv1.parent_view_id = sv2.parent_view_id
AND sv1.child_view_id = views1.id
AND sv2.child_view_id = views2.id
AND views2.view_subtype = 'fieldList'
AND attrs1.attribute_code = attrs2.attribute_code
AND attrs2.section_id = -1
AND attrs1.section_id != -1)