Oracle multi value parameter error

Question

I am using below query in SSRS 2008 and I am getting an error when I click the 'Next' button on the Design Query window in Report Wizard.

"An error occurred while the query design method was being saved. ORA-00911: invalid character"

  SELECT E.EMPID, E.NAME
    FROM EMP E
   WHERE E.EMPID IN (SELECT COLUMN_VALUE FROM TABLE(CAST(SPLIT(:I_IDS) AS TEMP_TBL)));

What am I doing wrong?

Sorry guys! Its was semi colon at the end.. that caused this error.. very sorry !! — CoolArchTek, Apr 26 '12 at 13:31
This has tripped me up before too. While the semi-colon is technically required in Oracle (even though various query tools let you omit it) it causes an error in SSRS. — Davos, Nov 28 '12 at 23:45

score 2 · Accepted Answer · answered Apr 24 '12 at 09:48

Have you tried simplifying your WHERE clause to use the condition:

WHERE E.EMPID IN (:I_IDS);

- in your query? (This won't work for multiple values in a normal SQL client, but does in SSRS datasets, in my experience.)

1 Answers1