Tasks Workspace SQL generates no data due to joins

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

No Rows to Show on Task Workspace due to faulty joins in the SQL query. Similar behaviour occured when going into Programs custom investments, no data is displaying.

STEPS TO REPRODUCE:

1. Go to Tasks workspace with admin user
2. Filter on Active = Yes, Template =No
3. Note the view returns all tasks
4. Now add any Project field to the view such as % Complete

Expected Results: To see same amount of rows

Actual Results: No Rows to Show

Environment

Release : 16.1.1, 16.2.x

DB: MSSQL 2019

Cause

ERROR 2024-07-31 18:24:41,667 [https-openssl-nio2-443-exec-297] odata.EntitiesResponseImpl (clarity:some_user:some_session:PPM_REST_API) (2be801bd-4776-4fb0-8665-9995c4d231ee) Exception reading objects: c_program

com.niku.union.persistence.PersistenceException:

SQL error code: 4104

Error message: The multi-part identifier "inv_investments.id" could not be bound.

Executed:

select odf_q.*  , (select top 1 full_name from ( SELECT  u.id AS user_id,

                u.user_name AS user_name,

                u.user_name AS UNIQUE_CODE,

                r.id AS resource_id,

                r.unique_name AS unique_name,

                r.first_name AS first_name,

                r.last_name AS last_name,

                r.full_name AS full_name,…

This is caused by ANSI_JOINS disabled in the system

Disabling the ANSI join feature was a temporary workaround for DE65166

Resolution

1. Check if the feature is disabled by running: 
   
   select name, enabled from cmn_features where code = 'S207096_ANSI_JOIN'
   
   If it's disabled, proceed with step 2
   
   
2. Run the below command in the database 
   admin toggle-feature S207096_ANSI_JOIN 1
3. Restart all services