Unable To Access Custom Object API-1019 Error

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When accessing a Custom Object, the following generic error is given:
API-1019: Could not process the request due to internal error.

ERROR 2022-11-21 08:37:54,380 [http-nio-8080-exec-712] ppm.rest (clarity:user:session:PPM_REST_API) RestAPILookupServiceImpl :: Problem occurred while reading lookup filter attributes for lookupType: Z_example

When trying to access the custom object attribute, the attribute was missing and corresponding error came:

ERROR 2022-11-21 13:40:31,108 [http-nio-8080-exec-758] ppm.rest (clarity:user:session:PPM_REST_API) Error while getting the attribute's caption
com.niku.union.persistence.PersistenceException: 
SQL error code: 0
Error message: ERROR: more than one row returned by a subquery used as an expression
Executed:
 SELECT
          odf_attr_metadata.id AS odf_pk,
          odf_attr_metadata.attribute_code AS attribute_code,
          (CASE
          WHEN ODF_ATTR_METADATA.IS_CUSTOM = 1 THEN (
          SELECT
          caName.attribute5
          FROM
          CMN_CAPTIONS_NLS caName,
          ODF_CUSTOM_ATTRIBUTES oca
          WHERE
          caName.pk_id = oca.id
          AND caName.language_code = ?
          AND caName.table_name = 'ODF_CUSTOM_ATTRIBUTES'
          AND (oca.OBJECT_NAME = ODF_ATTR_METADATA.OBJECT_CODE
          OR oca.OBJECT_NAME = ODF_ATTR_METADATA.INHERITED_FROM)
          AND oca.INTERNAL_NAME = ODF_ATTR_METADATA.ATTRIBUTE_CODE)
          ELSE (
          SELECT
          caName.attribute5
          FROM
          CMN_CAPTIONS_NLS caName
          WHERE
          caName.pk_id = ODF_ATTR_METADATA.id
          AND caName.language_code = ?
          AND caName.table_name = 'ODF_ATTR_METADATA')
          END) AS caption
          FROM
          odf_attr_metadata odf_attr_metadata,
          odf_objects odf_objects,
          odf_ca_odf_attr_metadata odf_ca_odf_attr_metadata
          WHERE
          odf_attr_metadata.object_id = odf_objects.id
          AND odf_attr_metadata.id = odf_ca_odf_attr_metadata.id
          AND odf_attr_metadata.OBJECT_CODE = ? 

When trying to access the object attribute, error:
ERROR System error. Contact system administrator.

ERROR 2022-12-13 09:59:40,181 [http-nio-8080-exec-689] niku.xql2 (clarity:user:session:odf.objectDefinitionAttribute) Internal Processing exception
com.niku.union.persistence.nsql.NSQLSyntaxException: NSQL Syntax exception: NSQL_SYNTAX_ERROR_DB_ERROR, Info: ERROR: column a.is_shared does not exist
  Position: 786
SQL Text:
 SELECT
q.id AS id,
q.code AS unique_code,
q.name AS name,
q.type AS type,
q.FULL_NAME AS created_by,
q.content_type AS content_type,
q.created_date AS created_date, 
q.last_updated_date AS last_updated_date  
FROM 
    ( 
SELECT 
a.id,
a.code,
....

    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: com.niku.union.persistence.PersistenceException: 
SQL error code: 0
Error message: ERROR: column a.is_shared does not exist
  Position: 786

Environment

Release : 16.0.3

Resolution

1. Review the lookup query in Administration > Data Administration > Lookups > Query tab
Copy/backup the NSQL.

2. Inactivate and delete the attribute from object attribute page.

3. Delete the lookup.

4. Create the lookup with corrected NSQL.

5. Create the object attribute .

6. Configure the attribute on the blueprint and publish.