Hello dbi-users,
I'm relatively new to Perl DBI so bear with me as this might be very
simple for the gurus.
I would like to use the results of a query as an input to another query's
WHERE ... IN clause. This is simple enough but I can't figure out how to
avoid extracting and formatting each row from the first query results.
That is, how can I avoid doing something tedious like this:
# ***** BEGIN EXAMPLE ************
my $sql1 = qq{SELECT ....};
my $sth1 = $dbh->prepare($sql1);
$sth1->execute();
my @results;
while ( my @row1 = $sth1->fetchrow_array ) {
push(@results, "\'" . @row1[0] . "\'");
}
my $resultsformatted=join(",",@results);
my $sql2 = qq{SELECT * FROM ... WHERE ... IN ($resultsformatted)};
# ***** END EXAMPLE ************
I tried "selectall_arrayref" but it doesn't help me since there is a
reference to each item in the array.
I tried "dump_results" but the output contains an extra row that gives the
number of rows dumped, hence the results can't be used in the second
query.
What I really want is a "neat_list" but I can't figure out how to assign
the results of the first query to an array that I can then pass to
"neat_list".
Something similar is possible in R/Splus using ODBC, the results of a
query can assigned to a data frame without accessing each row. How can I
do the same using Perl DBI?
Thanks in advance.
Daniel Ricard
