Clarity: ORA-00001: unique constraint (XXX.ODF_VIEW_ATTRIBUTES_U3) violated> Periodically, users are unable to modify portlet filter views.

Document ID : KB000051759
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

At times, duplicate records can get generated in the odf_view_attributes table (exact steps unknown).

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: Save to be processed.
Actual: Received the following error: "System error. Contact system administrator."

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

Executed:

update odf_view_attributes 
set display_order = ?, 
section_id = ?, 
col = ?, 
view_id = ?, 
last_updated_date = ?, last_updated_by = ? 
where id = ?

Solution:

Workaround:

Perfrom the following steps:

  1. Back up the odf_view_attributes table.

  2. Run the following script to delete the duplicates:
    DELETE 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)

Keywords: CLARITYKB, CLRT-20875, ODF_VIEW_ATTRIBUTES, duplicate, key.