Projects open up blank in MSP due to an incorrectly formatted NSQL query

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When exporting a project from Clarity to Microsoft Project (MSP), MSP opens a blank mpp file with the project ID. The error recorded in the app-ca logs:

ERROR 2018-11-16 07:44:13,802 [http-nio-14001-exec-34] niku.xql2 (clarity:admin:13146948__D36E2B32-5AB6-4D09-983D-6B27D0AC1357:schedulers.getProject) SQLException with query: .....

ERROR 2018-11-16 07:44:13,803 [http-nio-14001-exec-34] niku.schedulers (clarity:admin:13146948__D36E2B32-5AB6-4D09-983D-6B27D0AC1357:schedulers.getProject) Error during GetProjectRequest 
java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00907: missing right parenthesis 

or

ERROR 2021-03-25 13:25:07,403 [http-nio-8080-exec-849] niku.schedulers (clarity:admin:41467616__F3B4E011-6AB5-4434-B94B-0707AC80440C:schedulers.getProject) Error during GetProjectRequest
com.niku.union.persistence.PersistenceException: 
SQL error code: 0
Error message: ERROR: more than one row returned by a subquery used as an expression

Resolution

Generally the query used by the NSQL will be included in the app logs after the SQL exception part.

• The error "Oracle]ORA-00907: missing right parenthesis" indicates that there are spaces missing in the NSQL of a custom lookup. Once the NSQL is corrected, the projects will open in MSP including all tasks as expected.
• The error "ERROR: more than one row returned by a subquery used as an expression" is related to custom lookup based attribute that is returning a large number of duplicate rows, where the same is used in a process related to the project milestone creation. A separate lookup was introduced that would filter the duplicate. 

Additional Information

• MSP opening projects issues
• MSP Integration Known Issues