Solved

Multiple Parameters in Query

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…

739 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