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

    Question

  • 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.
    Kev.
    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