search cancel

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


Article ID: 187846


Updated On:


Clarity PPM On Premise Clarity PPM SaaS


  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, 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 = 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, 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


Release : All Supported Releases



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.