Re: SQL question with where in clause - Solved using ibatis

[Ashish Kulkarni <kulkarni_ash1312@xxxxxxxxx>]

Hi
Thanks for all the ideas, i had ibatis to the mix in
my application, and there is a feature in ibaits
called
Dynamic Mapped Statements which does all the work of
creating statement dynamically and it worked like a
charm.
if some on is interested let me know i will post the
way i defined in ibatis in this forum

Ashish


--- Larry Ducie <Larry_Ducie@xxxxxxxxxxx> wrote:

> Hi there Ashish,
>
> <snip>
> Hi
> I have a sql statment like
>
> select * from mytable where line in ('ABC', 'XYZ')
>
> where this SQL statement is going to extact all rows
> where line is ABC and XYZ,
>
> but now the problem is i do not know if there are
> going to be only one line or 2 or 10, so i thought i
> would define the sql as
>
> select * from mytable where line in ?
>
> and then i created a string like ('ABC', 'XYZ') and
> passed it as input parameter, but it does now work,
>
> Any ideas for a work around this solution
> </snip>
>
>
> There isn't really a graceful was to solve this
> problem. If you really want
> it variable then forget parameter markers completely
> and put your filters in
> a file and use that as a filter list:
>
> select * from mytable where line in (select distinct
> mylinefilters from
> mylinefiltersfile)
>
> Otherwise you will simply have to guess the maximum
> number of parameter
> markers you may ever need and simply repeat your
> last list item until you
> have used them all. Pretty easy to code in a for
> loop and the database is
> smart enough to tell that:
>
> select * from mytable where line in ('ABC', 'XYZ')
>
> is exactly the same as:
>
> select * from mytable where line in ('ABC', 'XYZ',
> 'XYZ', 'XYZ')   
>
> So you could use four parameter markers even if you
> will probably only use
> two parameter markers 98% of the time. They're just
> placeholders after all.
>
> Cheers
>
> Larry Ducie
>
>
> -- 
> This is the Java Programming on and around the
> iSeries / AS400 (JAVA400-L) mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/java400-l.


A$HI$H