Solved

Multiple Parameters in Query

Posted on 2006-06-20
4
610 Views
Last Modified: 2008-02-01
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
Comment
Question by:Charles Baldo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 16946218
(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
 

Author Comment

by:Charles Baldo
ID: 16946257
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
 
LVL 4

Accepted Solution

by:
dnsvh earned 250 total points
ID: 16946966
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
 

Author Comment

by:Charles Baldo
ID: 16947760
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question