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

book

Article ID: 182985

calendar_today

Updated On:

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.

I am getting the error below. Can you please help to resolve this? (Please note ODFP"."Z_PORTFOLIO_PRIO  is valid identifier )

ERROR 2019-07-17 09:25:55,939 [http-nio-8080-exec-89] union.persistence (clarity:wiproadmin:33185811__A0C6EE81-0B72-42FD-8362-96B28AA65545:npt.graphFilter) 
java.sql.SQLSyntaxErrorException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "ODFP"."Z_PORTFOLIO_PRIO": invalid identifier
ERROR 2019-07-17 09:25:55,939 [http-nio-8080-exec-89] dal.NSQLClient (clarity:wiproadmin: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.Z_PORTFOLIO_PRIO is null OR (@WHERE:PARAM:USER_DEF:STRING:odfp.Z_PORTFOLIO_PRIO:[email protected]) )

Cause

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

Environment

All Clarity PPM versions

Component : Clarity PPM STUDIO

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:[email protected],
        @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:[email protected],
        @SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):PROJECT_COUNT:[email protected]
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:[email protected]))
AND      @[email protected]
GROUP BY R.FULL_NAME,
         MR.FULL_NAME
HAVING   @[email protected]