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

Richard Kuhler wrote:

> 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
>

I agree with Richard. Stay with what you now have as it is working.

Daniel Morgan

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)