Load Data Warehouse Fails with error on DWH_PROJECT_V

Products

• Clarity PPM SaaS

Issue/Introduction

After upgrade to 16.3.1 the load data warehouse job may fail with an error similar to

SELECT CASE WHEN a.record_count > 0 THEN 1 ELSE 0 END record_exists FROM (SELECT COUNT(1) record_count FROM DWH_PROJECT_V src WHERE (CLARITY_UPDATED_DATE::TIMESTAMP IS NULL OR CLARITY_UPDATED_DATE::TIMESTAMP >= to_timestamp('1910/01/01 12:00:00', 'yyyy/MM/dd HH24:mi:ss'))) a ERROR: more than one row returned by a subquery used as an expression     ....  ERROR: more than one row returned by a subquery used as an expression

Environment

Clarity 16.3.1 

Cause

16.3.1 added additional attribute on the project object for odf_obs_fin_dept

There may be some projects, especially older ones where there are two entries in the prj_obs_associations table.

This could happen in the Department OBS and the Financial Department are not in sync.

Resolution

Run the query found at

 

                
 select inv.code, poa.unit_id, pou.name, pot.unique_name
from prj_obs_associations poa, inv_investments inv, prj_obs_units pou, prj_obs_types pot, prj_obs_object_types poot, entity e 
where poa.table_name = 'SRM_PROJECTS' 
and poa.unit_id = pou.id 
and pou.type_id = pot.id 
and pot.id = poot.type_id 
and inv.id = poa.record_id 
and e.org_chart_obs_type_id = pou.type_id 
and poa.record_id in (SELECT poa.record_id 
FROM prj_obs_associations poa, 
prj_obs_units pou, 
prj_obs_types pot, 
prj_obs_object_types poot 
WHERE poa.unit_id = pou.id 
AND upper(poa.table_name) = 'SRM_PROJECTS' 
AND pou.type_id = pot.id 
AND pot.id = poot.type_id 
AND upper(poa.table_name) = upper(poot.table_name) 
AND poot.assoc_att_code = 'odf_obs_fin_dept' 
GROUP BY poa.record_id HAVING COUNT(poa.record_id)>1) 
group by inv.code, poa.unit_id, pou.name, pot.unique_name

If records are returned you will need to delete the incorrect association

Additional Information

Reference original KB for the issue in Clarity UI.

 

Error 500 error because of duplicate OBS prj_obs_associations