Projects open up blank in MSP due to an incorrectly formatted NSQL query
Article ID: 123015
Updated On:
Products
Issue/Introduction
When exporting a project from Clarity to Microsoft Project (MSP), MSP opens a blank mpp file with the project ID. The error in the error recorded in 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
Environment
Clarity PPM 15.9.1
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 " indicate 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" was related to custom lookup based attribute that was returning large number of duplicate rows, and same was used in a process related to the project milestone creation. A separate lookup was introduced that would filter the duplicate.
Additional Information
Feedback
