Specific portfolios throw Error: ORA-00936: missing expression due to OBS issue


Article ID: 17080


Updated On:


Clarity PPM On Premise


Specific portfolios throw error: ORA-00936: missing expression when I try to sync them. 

This is an example of the usual error message:

Job Completed 1/31/17 1:32 PM
SQL error code: 936
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00936: missing expression

 select odf_q.* , (select investment_type_name from ( SELECT o.code investment_type_code,
c.name investment_type_name,
 c.last_updated_date last_updated_date,
lang.id language_id,
 lang.language_code language_code
FROM odf_objects o,
 cmn_captions_nls c,
 odf_object_extensions oe,
 cmn_languages lang
WHERE c.pk_id = o.ID
AND 1=? and 1=1 and 1=1 AND c.table_name = 'ODF_OBJECTS'
AND c.language_code = ?
AND oe.extension_code = 'inv'
AND lang.language_code = c.language_code
AND lang.language_code = ?
AND o.is_customizable = 1
AND oe.object_code = o.code) q_investment_type where rownum = 1 and q_investment_type.investment_type_code = odf_q.investment_type) investment_type_caption , (select NAME from cmn_lookups_v where language_code = ? and lookup_type = ? an
Error 1/31/17 1:32 PM
Portfolio Sync failed:
SQL error code: 936
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00936: missing expression


If the OBS Unit was at some point edited and renamed after the power filter expression was created, the portfolio synchronization will fail.


All Supported Releases


Rebuild the Power Filter:

1. Edit the portfolio affected

2. Go to the Contents Editor tab

3. Check the power filters added for the investment inclusions

4. Copy the power filter expression to Notepad++

5. Check if the OBS field is part of the expression

6. If the OBS is part of the condition, please ensure that the OBS Unit exists.

8. Ensure you rebuild the power filter expression so the OBS Unit is updated as appropriate

Note: A couple of defects have been fixed that cause similar errors:

DE35200 - fixed in 15.3 - If Auto Suggest is used to choose the OBS the expression is not built correctly and Portfolio Sync Errors with Unknown Error.

DE48072 - fixed in 15.6.1 - Synchronize Portfolio job will fail with Unknown Error if OBS is chosen from the wrong level.