if the second query is an Oracle, you can query across to almost all other databases from an Oracle connection if you set it up. http://www.oracle.com/technology/products/oracle9i/datasheets/gateways/gateway_rel2_ds.html
or iterate over the first result set inserting rows into a global temp table in the other db (oracle), and than use the approach the other person gave you "select * from table where column in (select values from global_temp_table) Job [EMAIL PROTECTED] wrote: Hello, I forgot to mention, the first query is from a different database than the second one. Unfortunately, a subquery is not an option. D > Daniel, > > Why not use a subquery? It'll be more efficient than using the array. > > Something like "SELECT * FROM ... WHERE ... IN (SELECT ...)"; > > > HTH, > Anna > > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 25, 2005 11:40 AM > To: dbi-users@perl.org > Subject: How to store query results in an array? > > 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 > > > --------------------------------- Do You Yahoo!? Yahoo! Small Business - Try our new Resources site!
