Multi-lookup String Data Type Parameter in NSQL.

[Fernando Chitiva]

I need to use a character type parameter that allows me to filter between several options, but in the NSQL the parameter that I define does not allow me to set it to multiple selection.

This is the segment of my NSQL that has the parameter definition:

INNER JOIN niku.ODF_CA_PROJECT ODF_CA_PRINNER JOIN niku.ODF_CA_PROJECT ODF_CA_PR ON (ODF_CA_PR.ID = INV_INV.ID) INNER JOIN niku.ODF_CA_INV ODF_CA_I ON (ODF_CA_I.ID = INV_INV.ID) INNER JOIN niku.CMN_LOOKUPS_V CMN_LU ON (CMN_LU.LOOKUP_CODE = ODF_CA_PR.AJV_LKP_CLASE AND CMN_LU.LOOKUP_TYPE = 'AJV_LKP_CLASE' AND CMN_LU.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@ AND CMN_LU.ID = @WHERE:PARAM:XML:STRING:/data/aj_projtypeid/@value@)WHERE INV_PRO.IS_TEMPLATE = 0 AND (INV_INV.IS_ACTIVE = 1 OR INV_INV.IS_ACTIVE = 0) AND INV_INV.ODF_OBJECT_CODE = 'project' AND INV_INV.STAGE_CODE IS NOT NULL AND ODF_CA_PR.AJV_LKP_CLASE IS NOT NULL AND PAC_MNT_PRO.COST_TYPE = 'CAPITAL' AND INV_INV.STAGE_CODE IN('CSK_INITIATION', 'CSK_PLANNING', 'CSK_BUILDING', 'CSK_CLOSING') AND (YEAR(INV_INV.SCHEDULE_START) <= ISNULL(@WHERE:PARAM:USER_DEF:INTEGER:YEAR@, YEAR(GETDATE())) AND YEAR(INV_INV.SCHEDULE_FINISH) >= ISNULL(@WHERE:PARAM:USER_DEF:INTEGER:YEAR@, YEAR(GETDATE()))) AND (PAC_MNT_PRO.AJV_GEST IS NOT NULL) AND ODF_CA_I.OBJ_WORK_STATUS <> 'CANCELLED' AND (PAC_MNT_PRO.AJV_GEST IN (@WHERE:PARAM:USER_DEF:STRING_LIST:STRATEGIC_GROUP@) OR @WHERE:PARAM:USER_DEF:STRING_LIST:STRATEGIC_GROUP@ IS NULL)

In the attributes of the NSQL I do not see the option to define it as multiple selection:

In my Portlet adding the search field appears to me as a single selection:

This is how it looks in the Portlet:

I need to be able to select several values from that list and that they reach my NSQL as a parameter.

Any ideas?

[David Morton]

Yes - there are some old (very detailed) discussions on this subject, I'll see if I can find them.

If I recall correctly though, the gist of it was;

• If your filter field is one of the returned columns (rather than a user-parameter field) - then this is easy, it should just work.
• If your filter field is a user-parameter, then this is also possible under some circumstances, but "fiddly" and you really have to aware of how the NSQL gets turned into SQL

So if you can code your query in the first way (as a returned column rather than a user-parameter) that's the easiest solution.

[David Morton]

OK, this is the old thread I was thinking of ; https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?GroupId=1747&MID=729849