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.