Is it possible to use a variable (table of numbers) with 'in' operator?

I have a table MOZ_WYP:
 CREATE TABLE moz_wyp (

   fk1  NUMBER        NOT NULL,
   fk2  NUMBER        NOT NULL);

Let's say it containst this data:
 FK1,FK2

 1,0
 1,3
 2,1
 2,2
 2,3
 3,0
 3,1
 3,2

The task is to find all FK1 that have given set of FK2 values. I do this with the following SQL:

• we look for fk1's that are connected with both 0 and 3 FK2 values SELECT fk1 FROM moz_wyp t WHERE fk2 IN (0, 3) GROUP BY fk1 HAVING COUNT (*) >= 2 FK1 --- 1

So far so good.

Now I'd like to have a function that would take a table of numbers as input parameter, return result set (cursor reference).

function get_fk1(at_fk2s in <table_of_number_type>, ac_cur out <cursor_ref_type>)

It should do something like this

• pseudo-code SELECT fk1 FROM moz_wyp t WHERE fk2 IN (at_fk2s) GROUP BY fk1 HAVING COUNT (*) >= at_fk2s.count

Is it possible to use a variable (table of numbers) with 'in' operator? Or should I use dynamic SQL?

TIA,
kazelot Received on Tue Mar 19 2002 - 07:24:49 CST