0 Replies Latest reply: Dec 20, 2012 12:21 AM by 978906 RSS

    Invalid Number in multi select- BI Publisher 10.1.3.4

    978906
      Hi all, I am using the below query in BIP and it throws 'Invalid Number' error. The same query works fine in Toad. Can somebody tell me why I get ORA-01722: invalid number
      We are using String datatype for the parameter ref_std

      Here is the gist of what values each contain
      multilist01 - contains csv values like ',100,200,300,400,'
      *: ref_std - will be a parameter(multi select) like EN 60439-1:1999,EN 60439-3:1991*


      -----
      select * from
      (
      select cert.multilist01
      from
      some_table
      ) xx
      join
      (

      select id as input from some_table where item_number in
      (
      select regexp_substr(:ref_std,'[^,]+', 1, level) ID
      from dual
      connect by regexp_substr(:ref_std,'[^,]+', 1, level) is not null
      )

      )yy on xx.multilist01 like '%'||yy.input||'%'
      -----