Home » SQL & PL/SQL » SQL & PL/SQL » Pass two or more value (Microsoft Visual Studio)
Pass two or more value [message #662732] Wed, 10 May 2017 21:58 Go to next message
jen888
Messages: 2
Registered: May 2017
Junior Member
In SSRS (Microsoft Visual Studio) I have a parameter pass policyno. The policyno may be one or two, I use split function to separate two policyno. Like: split(Parameters!policyNumber.Value,","). The where clause in my Oracle stored procedure as
Where policyno in (v_policyno) but it returns no data with two policyno, it works fine with one policyno. How can I correct it?

Thanks!
Re: Pass two or more value [message #662733 is a reply to message #662732] Thu, 11 May 2017 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 21358
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If maximum number of parameters is 2, perhaps the simplest option is to use two parameters in Oracle's WHERE clause, such as:
where  policyno = par_policyno_1
  and (policyno = par_policyno_2 or par_policyno_2 is null)

Otherwise, search for "varying elements in IN list" (you'll find references on OraFAQ forum, as well as the Internet).

[Updated on: Thu, 11 May 2017 00:10]

Report message to a moderator

Re: Pass two or more value [message #662776 is a reply to message #662732] Thu, 11 May 2017 22:37 Go to previous messageGo to next message
jen888
Messages: 2
Registered: May 2017
Junior Member
Thank you Littlefoot for the quick response. That is not an option for me. I had the report worked but when my co-worker reviewed the code he said he don't want to see the hard code, and I have four parameters.

I search for the "varying elements in IN list" and find Tom's post as:
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061

It doesn't work either.

Thanks.
Re: Pass two or more value [message #662797 is a reply to message #662776] Fri, 12 May 2017 06:40 Go to previous message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why doesn't the asktom solution work?
Previous Topic: Using REGEXP_LIKE
Next Topic: Trigger to join and insert data into table.
Goto Forum:
  


Current Time: Sun Feb 18 21:40:54 CST 2018

Total time taken to generate the page: 0.06042 seconds