Passing Multiple Values in a Parameter

[montac9169]

I'm looking to return values based on the multiple last names that are entered in the _last_name parameter. How can I make this simple select work?

select *
from sti_people_v
where last_name in _last_name

Thanks.
Los

 

[Ascalonian]

SELECT * 
  FROM sti_people_v
 WHERE last_name IN (:p_last_name1, :p_last_name2, :p_last_name3)

 

[SantaMufasa]

Mikey,

I believe that Los is saying that the list of all last names that should display are strung together (perhaps with delimiters, perhaps not) in the SQL*Plus variable named "_last_name". Therefore, there is no collection of variables named "_last_name1, _last_name2, _last_name3" (Is that correct, Los?)

If my presumption is correct, then this code should do what you want, Los:

select *
 From sti_people_v
 WHERE instr(upper(:p_last_name),upper(last_name)) > 0;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Ascalonian]

Ah... I read it wrong. However, after re-reading it, I think Los is saying there are multiple last names in _last_name and needs to find all of them.

My question at this point would be: How are you populating _last_name and how is it used?

 

[SantaMufasa]

I think Los is saying there are multiple last names in _last_name and needs to find all of them.

...And my code will list a separate row for each of the multiple last names that appear in _last_name. Here is an example of the same code working against my table of 25 employees:

select count(*) from s_emp;

  COUNT(*)
----------
        25

SQL> var p_last_name char(1000)
SQL> exec :p_last_name := 'Menchu,Velasquez,Ngao';

PL/SQL procedure successfully completed.

SELECT last_name
  FROM s_emp
 WHERE instr(upper(:p_last_name),upper(last_name)) > 0
/

LAST_NAME
------------
Velasquez
Ngao
Menchu

The above also shows a method of populating _last_name with multiple last_name values.

Let us know if this clarifies things a bit.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[montac9169]

Thank you both for the feedback. The parameter is being populated on a .net page. I will try the above code and let you know how it turns out. Should I resort to using a lexical parameter?

Los