This discussion is archived
2 Replies Latest reply: Mar 11, 2013 7:18 PM by 779257 RSS

Matching against a multi-select list

779257 Newbie
Currently Being Moderated
Hello all,

I have a 'Select List' widget (P_FILTER) which I have set to return multiple values.

In my report region, I have something like this

Select A from B where B.Col_1 IN upper(:P_FILTER)

When user selects only 1 value, the report is correct but if user selects more than 1 value (e.g. 2), the report does not return any rows. How do I get the report to recognize the multiple values returned by the LOV?
  • 1. Re: Matching against a multi-select list
    fac586 Guru
    Currently Being Moderated
    >

    Please update your forum profile with a real handle instead of "user9545618".
    I have a 'Select List' widget (P_FILTER) which I have set to return multiple values.

    In my report region, I have something like this

    Select A from B where B.Col_1 IN upper(:P_FILTER)

    When user selects only 1 value, the report is correct but if user selects more than 1 value (e.g. 2), the report does not return any rows. How do I get the report to recognize the multiple values returned by the LOV?
    In
    select * from emp where empno in (7788, 7839, 7876)
    <tt>(7788, 7839, 7876)</tt> is an expression list and the predicate is evaluated as a membership condition.

    In
    Select A from B where B.Col_1 IN upper(:P_FILTER)
    <tt>:P_FILTER</tt> is a scalar string, incapable of containing multiple values.

    See +varying elements in IN list+ on Ask Tom, and +emulating string-to-table functionality using sql+ for potential solutions.

    If the number of rows in <tt>B</tt> is large, then you need to use a technique that ensures that any indexes on <tt>B.Col_1</tt> are utilised.
  • 2. Re: Matching against a multi-select list
    779257 Newbie
    Currently Being Moderated
    I used the link to the entry on Ask Tom but modified it to look for colons instead of commas (I noticed the LOV seperated the multiple values by colon and not comma)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points