my @values = qw/Otago Auckland Nortland/;

#Quote Values to be safe
my $values = 
  join("," 
       ,map { $dbh->quote($_) } @values 
      );

my $statement = "
  SELECT c_email, c_first, c_last 
    FROM tblClient, tblGroup, tblRegion 
   WHERE  tblGroup.g_name = 'motel' 
     AND tblRegion.r_name IN ( $values )";

my $sth = $dbh->prepare($statement);
$sth->execute();
[download]

HTH

-enlil

For those who like bind variables:

my $placeholders = join ( ',', ('?') x @values );
my $statement = <<"EOF";
  SELECT c_email, c_first, c_last 
    FROM tblClient, tblGroup, tblRegion 
   WHERE  tblGroup.g_name = ?
     AND tblRegion.r_name IN ( $placeholders )
EOF
my $sth = $dbh->prepare($statement);
$sth->execute('motel', @values);
[download]

# assume that $dbh is ready...

my @places = qw/Otago Auckland Northland Boolaboola/;

my $query = <<ENDSQL;
SELECT c_mail, c_first, c_last
FROM tblClient, tblGroup, tblRegion
WHERE tblGroup.g_name = 'motel'
AND tblRegion.r_name IN
ENDSQL

my $sth = $dbh->prepare( $query . ' (' . join(',',map{'?'} @places) . 
+')' );

$sth->execute( @places );
[download]

The @places array dictates how many placeholders are added for the "IN (...)" clause, as well as providing the placeholder values for the execute call, so if you change the number of elements in the array, the sql stuff will follow through without further ado.

Hi There,
I am working on a query that will dynamiclly use an array.  I want to 
+put the process into a while loop and pass one value at a time to the
+ where clause.  I have tried this a couple of ways with placeholders.
+  For some reason the process only returns the first index of the arr
+ay.  As the while loop iterates through the index, I print out the cu
+rrent array index and it works.  I even pass the array value to the e
+mail / attachment and it works.  It seems like the prepare statement,
+ value, and placeholder get saved once.  When the test reports are se
+nt out...only the first report as data from the first array index.  I
+s there a way to change this....Can I use the below process and put i
+t into a while loop?  Does Perl allow this?  Thanks in advance....  I
+ am at home...and I forgot my login...my user name is cocl04...or ema
+il me at cory.clay@rentacenter.com or cocl04@yahoo.com...Please help.
+..I have been working on this for day...and I came across this postin
+g tonight...Thanks...
[download]

i.e.....

my $i = 0;

while (loop...)}
# assume that $dbh is ready...

my $value = $places[$i];

my $query = <<ENDSQL;
SELECT c_mail, c_first, c_last
FROM tblClient, tblGroup, tblRegion
WHERE tblGroup.g_name = 'motel'
AND tblRegion.r_name =
ENDSQL

my $sth = $dbh->prepare( $query . ' (' . join(',',map{'?'} $value) . 
+')' );

$sth->execute( $value );
$sth->finish;
$dbh->disconnect;
$i++;
}
[download]

Various bits of advice in no particular order:
• If you forgot your login password, there's a link on the Monastery home page (The Monastery Gates) for getting a password reminder via email -- it's easy. Use that.

• Don't put <code> tags around your entire post -- only use them around actual code and data. Everything else uses normal HTML tags (<P>, <UL>, etc). It really helps to make your posts as legible as possible; use a few iterations of "preview" if you have to, because it's worthwhile.

• You said:

  I want to put the process into a while loop and pass one value at a time to the where clause.

  Why do you want to use a while loop? Why not foreach or map? If you have the list of things already in an array, it makes more sense to use foreach or map.

• You said:

  As the while loop iterates through the index, I print out the current array index and it works.

  If that's true, then you did not post the code that works. The code you posted has syntax errors and does not include any print statements. Post code that you have actually tried (and that we can try also).

• In the code you posted, you are re-creating the entire SQL statement, and executing the statement, on every iteration of the loop. This contradicts what you seem to want to do, and totally defeats the purpose of the code you copied from my earlier post.

Try to express yourself more clearly. Try to think more clearly. What information does your script start with? What information do you want it to produce as output? How do you come up with that extra information? It looks to me like you are having equal amounts of trouble with Perl syntax and with your overall conception of the task.

UPDATE: I just noticed that you have posted the exact same body of messy nonsense as replies to several other nodes in this thread. DON'T DO THAT. Apart from the fact that those others will be even more puzzled than I was by your reply (because you were copying from my posted code, not from theirs), it's very likely they would have seen your single reply to me, because many monks use the Monastery services "Newest Nodes" and/or "Recent Threads" to keep up with ongoing dialogs.

As 1st reply pointed out, IN is the way to go, just remember that a SQL statement can contain at most 4000 chars. So you need some logic there to control the length, and break into multiple statements if needed.

just remember that a SQL statement can contain at most 4000 chars.

Which database has that limitation?

I've googled a bit, and I think that you have that statement mixed up with '(some char column type(s)) can be at most 4000 characters', which would mean that each bind variable could be at most that length.

The limitation for Microsoft SQL 2000 is 4000 characters. that's unicode, and we are talking about 8000 octets.

I could not remember the exact length limitation for Oracle, but my pression is that it is 3000+ characters. (The Oracle I use is a bit old, version 8.1.7)