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

Take a look at EXISTS

"kazelot" <kazelot_at_thenut.eti.pg.gda.pl> wrote in message news:8a47e227.0203190524.eda74aa_at_posting.google.com...
> 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 - 09:28:29 CST