Re: best way to parameterize an "in (...)" part of query?
From: William Robertson <williamr2019_at_googlemail.com>
Date: Mon, 2 Mar 2009 02:13:59 -0800 (PST)
Message-ID: <8ac815a2-318d-4094-894a-11bcc95553fd_at_x38g2000yqj.googlegroups.com>
On Mar 1, 10:42 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 01.03.2009 10:23, Maxim Demenko wrote:
>
>
>
> > m..._at_pixar.com schrieb:
> >> 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
Date: Mon, 2 Mar 2009 02:13:59 -0800 (PST)
Message-ID: <8ac815a2-318d-4094-894a-11bcc95553fd_at_x38g2000yqj.googlegroups.com>
On Mar 1, 10:42 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 01.03.2009 10:23, Maxim Demenko wrote:
>
>
>
> > m..._at_pixar.com schrieb:
> >> 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
The cleanest and simplest approach might be to pass a collection into the procedure and replace the IN clause with MEMBER OF. I'm not sure how well that scales for large collections though. Received on Mon Mar 02 2009 - 04:13:59 CST