Here's a fun and slightly obfuscated method to do that:
my $usql=q{select username from dba_users};
my $aryRef = $dbh->selectall_arrayref($usql);
my @users = map { $aryRef->[$_][0] } 0..$#{$aryRef};
my $newSql = q{select from users where username in ('}
. join(q{','},@users) . q{')};
print "$newSql\n";
Jared
On Wed, 2005-05-25 at 11:39, [EMAIL PROTECTED] wrote:
> 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
>
