2 Replies Latest reply: Mar 11, 2013 9:18 PM by 779257 RSS

    Matching against a multi-select list

    779257
      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
          >

          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
            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)