Oracle with Reporting Services. "illegal variable name/number"


  • I'm developing a report with SQL Server Reporting Solutions (obviously ;)) against an Oracle DB and have run into a problem.

    This is the code that I'm using for the dataset query that my report is to be based on:

    ="SELECT * FROM IRMFE_*************_V WHERE " &
      IIf(Parameters!Invoice_No.Value(0)<> "ALL", "SUPPLIER_INVOICE_NO IN (:InvNo)", "SUPPLIER_INVOICE_NO LIKE '%'")

    The parameter ":InvNo" is a paramter in the report and the user can select multiple values. I also included an "ALL" option manually in the query for the parameter. The query works fine with single or multiple options but if I select "ALL" I get the following error:

    "Query execution failed for dataset 'FREIGHT'.
    ORA-01036: illegal variable name/number'

    There seems to be no problem when I manually run the query (SELECT * FROM IRMFE_*************_V WHERE SUPPLIER_INVOICE_NO LIKE '%')

    The reason I used "SUPPLIER_INVOICE_NO LIKE '%'" is because I'll be adding a few more parameters and wanted to avoid any problems with adding "AND" between the conditions.

    Any resolution or suggestion on this would be much appreciated guys.
    Tuesday, July 25, 2006 9:36 AM

All replies

  • After 1/2 of bangin my head against the wall I've resolved it. The reason it wasn't picking it up in the first place was that the check should have been:

    IIf(Parameters!Invoice_No.Value(0)<> "'ALL'"

    instead of

    IIf(Parameters!Invoice_No.Value(0)<> "ALL"

    I found Chris Hays solution and will just use that though.
    Tuesday, July 25, 2006 9:56 AM