Re: best way to parameterize an "in (...)" part of query?
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 01 Mar 2009 10:23:10 +0100
Message-ID: <49AA53FE.80404_at_gmail.com>
mh_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?
>
> Many TIA!!
> Mark
>
Date: Sun, 01 Mar 2009 10:23:10 +0100
Message-ID: <49AA53FE.80404_at_gmail.com>
mh_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?
>
> Many TIA!!
> Mark
>
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:
- select foo from bar where instr(','||INLIST||',',','||x||',') > 0
- 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)
David Fitzjarell has published on his site an approach as well http://oratips-ddf.blogspot.com/2008/09/how-dynamic.html
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
Best regards
Maxim Received on Sun Mar 01 2009 - 03:23:10 CST