parameter in sql query :SSRS

Question

Hi, i am using oracleclient peovider. i was wondering how do i use a parameter in the query.

select * from table A where A.a in ( parameter).

parameter should be a multivalues parameter.

how do i create a data set.

Thanks. Susan

Answer 1

I don't think you can use a parameter in such a situation. (Unless oracle and the language you're using supports array-type parameters ? )

Answer 2

Simple. Add the parameter to the report and make sure to check it off as multi-valued. Then in the data tab and go in and edit the query click the "..." button to edit the dataset. Under the parameters tab create a mapping parameter so it looks something like this (obviously you will have different names for your parameters):

@ids | =Parameters!ContractorIDS.Value

Then in the query tab use the coorelated sub-query like your example above. I have done this many times with SQL server and there is no reason it should not work with Oracle since SSRS is going to build an ANSI compliant SQL statement which it will pass to Oracle.

where A.myfield in (@ids)

Answer 3

You can't have a variable in list in oracle directly. You can however, break apart a comma seperated list into rows that can be used in your subquery. The string txt can be replaced by any number of values seperated by a comma.

   select * from a where a.a in ( 
       SELECT regexp_substr(txt,'[^,]+',1,level)
         FROM (SELECT 'hello,world,hi,there' txt  -- replace with parameter
                 FROM DUAL)
   CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (txt, '[^,]'))+1
   )

The query works by first counting the number of commas that are in the text string. It does this by using a reqular expression to remove all non commas and then counts the length of the remainder.

It then uses an Oracle "trick" to return that number + 1 number of rows from the dual table. It then uses the regexp_substr function to pull out each occurence.