Project and Portfolio Management Practitioners Forum
cancel

Custom Portlet SQL for Multi-Select to Multi-Select

Highlighted
Abdullah Rafiq
Respected Contributor.

Custom Portlet SQL for Multi-Select to Multi-Select

I am very familiar on creating custom Portlets in PPMC using SQL, creating filters etc. However, I recently have come across a scenario and am having difficulty finding a working solution.

Scenario:
Request Type with a field 'A' which is multi-selct and allows user to enter in more than 1 value.

Portlet Filter field 'A' which is also multi-select and allows user to enter in more than 1 value.

The normal SQL statement for this filter field 'A' would be something like:
"and ([TABLE_NAME].VISIBLE_PARAMETERXX) IN ([VP.TOKEN_FIELD_A.TO_STRING])"

Let us assume that this translates to
"and (Value1, Value2) in (Value3, Value2, Value1)"

This does not translate properly in SQL as the oracle IN clause does not compare multiple values against multiple values. It only works with a single value against multiple values.
What solution would you recommend to get this scenario to work? Is there another type of SQL filter that I am not aware about?

Appreciate your help.
Thanks,
Abdullah