1 Reply Latest reply on Jul 18, 2018 8:24 AM by Martien van den Akker

    Multiple IN clause using DB Adapter




      I have a SQL query which has multiple IN clause for validation . I am trying to implement it using DB adapter but it's not working. Usually to use IN clause in DB adapter i will use sub-query like


      IN (WITH VALUELIST AS (SELECT ? val FROM dual) SELECT SUBSTR(val, (decode(LEVEL, 1, 0, instr(val, ',' , 1, LEVEL -1)) + 1), (decode(instr(val, ',' , 1, LEVEL) -1, -1, LENGTH(val), instr(val, ',' , 1, LEVEL) -1)) - (decode(LEVEL, 1, 0, instr(val, ',' , 1, LEVEL -1)) + 1) + 1) a FROM VALUELIST CONNECT BY LEVEL <= (SELECT(LENGTH(val) -LENGTH(REPLACE(val, ',' , NULL))) FROM VALUELIST) + 1)


      But this is not working for multiple IN clause scenario.

      SQL Query: SELECT column_name(s)

      FROM table_name



      Please help me to implement multiple IN clause using DB Adapter


        • 1. Re: Multiple IN clause using DB Adapter
          Martien van den Akker



          The database adapter actually does not support the IN clause. That's why people come up with such an intimidating work-around as you suggest here (it intimidates me at least...).

          So, I'm not fond in using this. To my experience, working with complex workarounds like this, usually is not the best solution. It does not make things very maintainable.


          What I would do is to create a view in which you would work the above around so that you can just provide a where clause in which you can do 'select ... from view where a=#parameterA and b=#parameterB' etc. Where those parameters could hold a comma-separated list of values.


          However, my approach would be to create a pl-sql (pipe-lined) function that would accept a set of collections of Oracle types and return the result as a collection of Oracle types with the columns defined as attributes.

          Then in that function you could implement the select as suggested above with as many in-constructions as you need.


          You could have string parameters to the function, in which you provide a comma-separated  list. But then in BPEL you need to build up that list. While with a collection-based parameter, you can just use a for-each in XSLT to do the mapping.


          Hope these suggestions helps you to get further.