RE: SQL question with where in clause

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

Ashish,

Instead of using place markers to "hold a spot" for a variable number of
values, you could build the entire SQL statement, including the actual
values, and then prepare that statement and execute it.

For example,

sqlStatement = "select * from mytable where line in (";

sqlStatement = sqlStatement + "'ABC',";
// Repeat previous statement for each value you need to check for. The last
value should not include the trailing comma after the close single-quote.

sqlStatement = sqlStatement + ")";

Then just prepare that statement and execute it.

Hope this helps!

Richard



-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of Ashish Kulkarni
Sent: Thursday, September 13, 2007 3:32 PM
To: Java Programming on and around the iSeries / AS400
Subject: Re: SQL question with where in clause


Hi
No the problem i have is, i dont know how many ? to be
added to my SQL statement
select * from mytable where line in (?,?,?)
will work if i know i will have only 3 values to
replace, but the number of lines selected are going to
come from the screen, which can be from 1 t more then
100, so i wanted to make this portion dynamic

Ashish
--- Michael_Schutte@xxxxxxxxxxxx wrote:

> I went back at old code... In places where didn't
> want to use the place
> marker, I used...
>
> cstmt.setNull( ... );
>
> However, I wasn't doing this with a select statement
> it was with a
> procedure call that populated databases.  But this
> may be a place to start
> looking at.
>
>
> Michael Schutte
> Admin Professional
> Bob Evans Farms, Inc.
> "The Secret's the Sauce! Enjoy our new Bob-B-Q
> Pulled Pork Knife & Fork
> Sandwich!"
>
>
> java400-l-bounces@xxxxxxxxxxxx wrote on 09/13/2007
> 02:48:26 PM:
>
> > Without seeing your code... I believe you are
> wanting to do this.  Are
> you
> > doing this?
> >
> > value1 = 'ABC';
> > value2 = 'XYZ';
> > value3 = '';
> > value4 = '';
> > value5 = '';
> > value6 = '';
> > value7 = '';
> >
> > sqlStatement = "{SELECT * FROM mytable WHERE line
> in (?,?,?,?,?,?,?)});
> > cstmt = conn.prepareCall(sqlStatement);
> > cstmt.setString(1,  value1);
> > cstmt.setString(2,  value2);
> > cstmt.setString(3,  value3);
> > cstmt.setString(4,  value4);
> > cstmt.setString(5,  value5);
> > cstmt.setString(6,  value6);
> > cstmt.setString(7,  value7);
> >
> > I cannot test this right now, but this is how I
> understand to use the "?"
> > place holders.
> >
> >
> >
> > Michael Schutte
> > Admin Professional
> > Bob Evans Farms, Inc.
> > "The Secret's the Sauce! Enjoy our new Bob-B-Q
> Pulled Pork Knife & Fork
> > Sandwich!"
> >
> >
> > java400-l-bounces@xxxxxxxxxxxx wrote on 09/13/2007
> 02:35:18 PM:
> > > 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
> > >
> > >
> > > Ashish
> > >
> > > A$HI$H

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.485 / Virus Database: 269.13.14/999 - Release Date: 9/10/2007
5:43 PM