Re: best way to parameterize an "in (...)" part of query?

>> I have two relatively large procedures that differ only
>> by the contents of an "in (...)" clause, e.g.
>>
>>     create proc1 as
>>     ...
>>        select foo from bar where x in ('a','b')
>>     ...
>>
>>     create proc2 as
>>     ...
>>        select foo from bar where x in ('x','y')
>>     ...
>>
>> I would like to parameterize this, so that proc1 and proc2 can
>> call a common subroutine, and pass in the "in" list.
>>
>>     create proc1 as ...  baseproc(['a','b'])  -- some representation 
>> of a,b
>>     create proc2 as ...  baseproc(['x','y'])  -- some representation 
>> of x,y
>>
>>     create baseproc(INLIST SOMETYPE)
>>     ...
>>        select foo from bar where x in INLIST
>>     ...
>>
>> How can I parameterize this?  Is there an appropriate type?

> I have serious doubts the best way exists, but there are many
> alternatives which you can implement. Besides that, the question you ask
> was very often discussed ( keyword: dynamic in list) here or at AskTom.
> Here are some approaches:
>
> 1) select foo from bar where instr(','||INLIST||',',','||x||',') > 0
> 2) select foo from bar where x in ( select
> regexp_substr(INLIST,'(^|,)(.*)(,|$)','\2',level,1)
> from dual
> connect by regexp_substr(INLIST,'(^|,)(.*)(,|$)','\2',level,1)
> is not null)

Another option: create a table type and use a query roughly like

select foo from bar where x in ( select column_value from table(array) )

> But, as i mentioned already, this question is probably asked as often as
> about pivot tables, so if you start to search, you'll find dozens of
> possibilities

That's true.

Kind regards

        robert Received on Sun Mar 01 2009 - 04:42:00 CST