Solved

Multiple Parameters in Query

Posted on 2006-06-20
4
604 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:charlesbaldo
  • 2
4 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
Comment Utility
(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:charlesbaldo
Comment Utility
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
Comment Utility
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:charlesbaldo
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now