Solved

Multiple Parameters in Query

Posted on 2006-06-20
4
605 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
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:charlesbaldo
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:charlesbaldo
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

862 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

25 Experts available now in Live!

Get 1:1 Help Now