Synchronize Portfolio Investments Job fails with error ORA-00936: missing expression due to power filter limit

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

1. The portfolio synch job is failing with the below error when we create a large power filter expression
2. Job fails if certain threshold is hit, e.g. 1400. 

UI Error:

APP error:

Portfolio Sync failed: SQL error code: 936 Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00936: missing expression
Executed: select odf_q.*  , (select name from ( select o.code code,  nls.name name,     nls.description description,o.LAST_UPDATED_DATE LAST_UPDATED_DATE  ,l.ID LANGUAGE_ID,  nls.LANGUAGE_CODE LANGUAGE_CODE  from  odf_objects o, cmn_captions_nls nls,CMN_LANGUAGES l     where  o.id = nls.pk_id     and    nls.language_code = ?     and    nls.table_name = 'ODF_OBJECTS'  AND    nls.LANGUAGE_CODE = l.LANGUAGE_CODE     and    1=? and 1=1 and 1=1) q_odf_object_code where rownum = 1 and q_odf_object_code.code = odf_q.odf_object_code) odf_object_code_caption  from ( select row_number() over ( order by odf_pk)  odf_row_num, count(*) over (partition by pmd_analytical_partition_by)  odf_num_rows ,odf_cols.* from (  select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk  , INV_INVESTMENTS.CREATED_DATE created_date , odf_ca_inv.odf_object_code  odf

• Note: The same issue happens if you create the power filter from the Contents Editor tab in the Add Investments section

Environment

Release : All Supported Releases

Component : CA PPM PORTFOLIO MANAGEMENT

Resolution

Below are best practice recommendations to add a large number of projects into a portfolio:

1. Use another field in the power filter to help pull in projects that all fall into the criteria such as OBS or a custom attribute
   • Example using a custom attribute:
     1. Create a custom attribute on the project object
     2. Assign all the projects that should be part of the same portfolio the same value for that custom attribute
     3. Then create a power filter in the portfolio based on that custom attribute 
2. Manually add the projects under the Individual Investments section of the Contents Editor tab in the Portfolio
   
   The portfolio error can result to also invalid characters caused by errant copy-and-paste.