Using "WHERE" with a variable that has multiple values

Question

I want to know how to select columns from a table where another column has several possible values. So, for example, this code will return Column X from table 1 and Column B from Table 2 if Column G has the value given by the input.

variable input varchar2(60);
exec :input := 'c';

Select Table1.x, Table2.b
  FROM Table1, 
       Table2
 WHERE Table1.g = :input

What if instead, I wanted to return Column X from table1 and ColumnB from Table2 if ColumnG had value "k" or "c" or "d" without doing

WHERE Table1.g = "k" or Table1.g = "c" or Table1.g = "d"

How do i define the input such that I get this effect?

I use: oracle 10g, PL/SQL

Does it have to be a bind variable? A substitution variable might make this very easy. — Jon Heller, Dec 14 '14 at 02:12

Nick Krasnov · Answer 1 · 2013-08-29T17:32:46.110

As I understood from your comment to @SuperMan answer

Yes, is there any way to define a variable to be a placeholder for the ('c', 'd', 'k')?

you want to parametrize IN clause.

Obviously you cannot pass the :input variable in the IN clause defined like this exec :input := 'k,c,d'. I mean, technically you can, but the query wont produce the desired output because it will search for the entire 'k,c,d' string.

What you can do is, you can split the 'k,c,d'(or any other) string by comma and use the result in the IN clause.

Here is an example:

-- variable of this type will contain letters produced by 
-- splitting 'k,c,d' string
create or replace type T_vars as table of varchar2(5)

-- This table function returns collection of individual letters
create or replace function GetLetters(p_input in varchar2)
return t_vars
is
  l_res T_Vars;
begin
  select cast(
              collect(
                       cast(res as varchar2(5))
                      ) as T_Vars
             )
    into l_res
    from ( select regexp_substr(p_input, '[^,]+', 1, level) as res
             from dual
          connect by level <= regexp_count(p_input, '[^,]+' )
          );

  return l_res;
end;

Here is how it works:

SQL> variable x varchar2(11);

SQL> exec :x := 'k,c,d';

PL/SQL procedure successfully completed

SQL> select col
  2    from table1
  3   where col in (select column_value from table(getletters(:x)))
  4  ;

Result:

 COL
 ---
 k
 c
 d

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

Looking for this?

Select Table1.x, Table2.b
FROM Table1, Table2
WHERE Table1.g  in ('c', 'd', 'k')

Edit - reply on comment: It is not straight forward or take a look here.

edited May 23 '17 at 10:25

Community

1
1

answered Aug 28 '13 at 11:50

SuperMan

178
1
1
7

Yes, is there any way to define a variable to be a placeholder for the ('c', 'd', 'k')? Thank you – RebeccaK375 Aug 28 '13 at 11:53

score 0 · Answer 3 · answered Aug 28 '13 at 12:38

Since you are using pl/sql a FOR loop can be used to run the query for each value. Something like this:

FOR x in ('k','c','d')
loop
Select Table1.x, Table2.b into var1,var2
FROM Table1, Table2
WHERE Table1.g = x
dbms_output.put_line(var1,var2);
end loop;

score 0 · Answer 4 · answered Aug 28 '13 at 20:18

Its pretty straight forward -

Select Table1.X, Table2.B FROM TABLE1, TABLE2 WHERE TABLE1.g IN ('c', 'd', 'k')

Basically it will give you a Cartesian product of all row from table 1 where Table1.g = c or d or k and all rows from Table2 since you don't have any another join condition.

score -1 · Answer 5 · edited Aug 28 '13 at 12:36

-1

Can you try this?

where table1.g in ("k","c","d")

edited Aug 28 '13 at 12:36

bummi

27,123
14
62
101

answered Aug 28 '13 at 11:52

Sander

390
1
4
13

It works, I just wonder if I can define a variable which will be a placeholder of the stuff in parenthesis. Thank you – RebeccaK375 Aug 28 '13 at 11:54

Using "WHERE" with a variable that has multiple values

5 Answers5