Multi-value param attribute in NSQL query not working as expected

Products

• Clarity PPM On Premise

Issue/Introduction

I am trying to pass multi-values to param attribute in NSQL query but its not working as expected.

ERROR 2019-07-17 09:25:55,939 [http-nio-8080-exec-89] union.persistence (clarity:someUser:33185811__A0C6EE81-0B72-42FD-8362-96B28AA65545:npt.graphFilter) 
java.sql.SQLSyntaxErrorException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "ODFP"."attributeabc": invalid identifier

ERROR 2019-07-17 09:25:55,939 [http-nio-8080-exec-89] dal.NSQLClient (clarity:someUser:33185811__A0C6EE81-0B72-42FD-8362-96B28AA65545:npt.graphFilter) Unable to retrieve NSQL cube.  
NSQLException was thrown.
com.niku.union.persistence.nsql.NSQLException: com.niku.union.persistence.PersistenceApplicationException

NSQL query is:

WHERE 1=1 AND (odfp.attributeabc is null OR (@WHERE:PARAM:USER_DEF:STRING:odfp.attributeabc:portfolio_prio@) )

Environment

All Clarity PPM versions

Component : Clarity PPM STUDIO

Cause

This issue is caused by the use of STRING construct in NSQL query.

Resolution

You need to use STRING_LIST construct and not STRING to pass multiple string values as input. With this change, the username lookup parameters work.

eg:

SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:RSRC@,
        @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:MANAGER@,
        @SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):PROJECT_COUNT:AGG@
FROM
    INV_INVESTMENTS I,
         SRM_RESOURCES R,
         SRM_RESOURCES MR,
         CMN_SEC_USERS U
WHERE    I.CREATED_BY = U.ID
AND      (MR.UNIQUE_NAME IN (@WHERE:PARAM:USER_DEF:STRING_LIST:managers@))
AND      @FILTER@
GROUP BY R.FULL_NAME,
         MR.FULL_NAME
HAVING   @HAVING_FILTER@