Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Scott Calnon
    Multiple Parameter List - With 2 Column Values Concatenated...
    Topic posted June 6, 2019 by Scott Calnon, last edited June 7, 2019, tagged BI Publisher, Fusion Financial reporting, Public Sector, Reports 
    44 Views
    Title:
    Multiple Parameter List - With 2 Column Values Concatenated (BI Publisher)
    Summary:
    ORA-00909: invalid number of arguments message when executing report with multiple parms.
    Content:

    It is a business requirement to have Cost Centre code and Description in the drop down list.  Ie.

    nnnn-<description>

    Example 1:

    1105-Chief Executives Office

    1108-Board & Chair

    When executing the BI publisher report and selecting multiple values the query (snippet) as follows has a ORA-00909 error. (codes.segment2 is the cost centre code eg. 1105   and the parameter :p_costcentre contains multiple values selected (see Example 1 above).

    ..
    AND (codes.segment2 IN (SELECT substr(token,1,instr(token,'-')-1)
             from  (select regexp_substr(:p_costcentre, '[^,]+', 1, LEVEL) token
                                                                 from dual
                                                                 connect by LEVEL <= length(:p_costcentre) - length(REPLACE(:p_costcentre,',','')) + 1
                      )
        )
    .
    .Is there any way to get around this error, without just listing the cost centre code on its own.
    Code Snippet: