AnsweredAssumed Answered

How to add 'optional' parameter to NSQL query in the portlet

Question asked by RK_L on Jul 10, 2014
Latest reply on Jul 10, 2014 by RK_L

At the very end of NSQL code, I am trying to add in a ‘Resource’ Parameter using following code:

 

where

( PRJ_TEAM_V2.REQUIREMENT_NAME IN

       ( 

          SELECT DISTINCT S.FULL_NAME REQUIREMENT_NAME

          FROM PRTEAM PR

          INNER JOIN SRM_RESOURCES S ON S.ID = PR.PRRESOURCEID

          INNER JOIN SRM_RESOURCES R ON PR.PRROLEID = R.ID

          WHERE R.ID IN (5202003, 5202009, 5202025, 5004026)

          AND S.FULL_NAME = @WHERE:PARAM:USER_DEF:STRING:RESOURCE@

       )

    )

 

If  I put the above code directly after the 'where' clause, then this parameter becomes mandatory.

Since I need it to be optional, if I use a case statement in the last line (like shown below), then

the query doesn't run because null (' ') string is being passed in case the user does not enter any

information for this parameter.

 

          AND S.FULL_NAME = case when @WHERE:PARAM:USER_DEF:STRING:RESOURCE@ IS NULL THEN '  ' ELSE  @WHERE:PARAM:USER_DEF:STRING:RESOURCE@ END

 

How to handle this issue? Please guide.

Outcomes