When attempting to XOG lookup from one environment to another it is throwing an error

Products

• Clarity PPM On Premise

Issue/Introduction

One of our custom dynamic lookup when attempting to XOG into another environment it is throwing error, somehow lookup changes are updated after XOG, this error is bothering:

Error message: ORA-01427: single-row subquery returns more than one row

Environment

Release : 15.6.1

Component : CA PPM XML OPEN GATEWAY (XOG)

Cause

      <ErrorInformation>
        <Severity>FATAL</Severity>
        <Description>com.niku.union.persistence.PersistenceException: 
SQL error code: 1427
Error message: ORA-01427: single-row subquery returns more than one row

Executed:
 UPDATE cmn_browse_portlet_attributes
    SET
    include_in_search_filter = ?,
    last_updated_by = ?,
    last_updated_date = ?
    WHERE id =
    (SELECT   c.id
    FROM     cmn_attributes a,
    cmn_list_of_values b,
    cmn_browse_portlet_attributes c
    WHERE    a.table_name = 'CMN_LIST_OF_VALUES'
    AND      a.pk_id = b.id
    AND      b.lookup_type_code = ?
    AND      c.attribute_id = a.id
    AND      a.column_name = ?) 

Resolution

As observed in the XOG output file the cause of the error is the query below:

 UPDATE cmn_browse_portlet_attributes
    SET
    include_in_search_filter = ?,
    last_updated_by = ?,
    last_updated_date = ?
    WHERE id =
    (SELECT   c.id
    FROM     cmn_attributes a,
    cmn_list_of_values b,
    cmn_browse_portlet_attributes c
    WHERE    a.table_name = 'CMN_LIST_OF_VALUES'
    AND      a.pk_id = b.id
    AND      b.lookup_type_code = ?
    AND      c.attribute_id = a.id
    AND      a.column_name = ?) 

Error message: ORA-01427: single-row subquery returns more than one row

What this means is subquery is expecting 1 record because it is an equal sign but in reality it is returning more then 1. Subquery is this one:

SELECT   c.id
    FROM     cmn_attributes a,
    cmn_list_of_values b,
    cmn_browse_portlet_attributes c
    WHERE    a.table_name = 'CMN_LIST_OF_VALUES'
    AND      a.pk_id = b.id
    AND      b.lookup_type_code = ?
    AND      c.attribute_id = a.id
    AND      a.column_name = ?

There is a possibility there is some orphan records for this particular lookup from previous issues with it or something else. So here is what we should do:

1. Set a SQL Trace for a user that you are using for XOG
2. Run same XOG input file
3. Capture trace file and what we need from there is to find that same query which is failing with all the parameters being sent so we can substitute ? with real values and try to run the
4. Customer was able to identify duplicate and resolve