• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 615
  • Last Modified:

Multiple Parameters in Query

Hello,

I am using SQL Reporting Services 2000 with Oracle database.

I need to pass in a parameter that will have 1 to many values.

The ORACLE SQL looks like this

SELECT
  i.J_no,
  l.assaynumber
FROM
   instrument i
WHERE
  i.J_no IN('34000701','34000702', ...list of many values ...)          //..... many possible values here
  and assaynumber IN(310,344,501 ...list of many values ...)                  //.....many possible values here)

 I tried something like this

WHERE
  i.J_no IN( :JVALUES )   where :JVALUES = '34000701','340002702','340000791'

but that did not work


Thank You
Chuck


0
Charles Baldo
Asked:
Charles Baldo
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Wild guess)  If the number has a numeric (i.e. not char or varchar) type, then lose the single quotes.

  i.J_no IN(34000701,34000702, ...list of many values ...)          //..... many possible values here
0
 
Charles BaldoSoftware DeveloperAuthor Commented:
Jim

Thanks for the input, but it is a string, I did try the other field assaynumber without quotes because it is numeric.

Thanks
Chuck
0
 
dnsvhCommented:
Chuck,

If the values that you are passing are somehow related, you could create a custom lookup table in Oracle and then do a join to it.  You cannot, however, pass multiple values in Reporting Services.

Another possible solution is that, if you know the maximum number of parameters that could be passed, you could create a variable for each one and then pass each one individially.

Hope that helps.

Derrick
0
 
Charles BaldoSoftware DeveloperAuthor Commented:
Thanks both for the comments. Unfortunatly This cant be done in my situation.

I did try jimhorn  suggestion but did not work. Derricks would work and I thought of that  however I am in an FDA Validated environment. Creating a new table would be about as simple as getting the Pope to say "Priests can Marry".

So I am going to use a data grid and fill it. They can export the grid to excel and do what the want with it.

Thanks
Chuck
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now