Re: select ... where col in (collection)

From: Richard Kuhler <noone_at_nowhere.com>
Date: Mon, 08 Sep 2003 21:46:29 GMT
Message-ID: <VM67b.20185$gt1.1087339@twister.socal.rr.com>

roger wrote:

>>
>>>Well, the answer to my question seems to be to
>>>do something like this:
>>>
>>>  where col in (select column_value from table(collection_variable) )
>>
>>As silly as it might seem, you have to cast the variable to it's own 
>>type to make this work ...
>>
>>... where user_id in
>>(select column_value from table(cast(cvar as numtab))))
>>
>>
>>Richard Kuhler
>>

> That does indeed seem silly, but it also works:)
> Thank you very much.
>
> Now, I guess my only question would be whether this is
> the best or only way to use a collection variable in
> this way - as the subject of an IN operator.
>
> Would you have any thoughts on that?
>
> Thanks again.

Given what I read your requirements to be, this is the best approach. In my experience, the only problem you may encounter is that these types of queries can be more difficult to tune if they get complex.

Richard Kuhler Received on Mon Sep 08 2003 - 16:46:29 CDT