RE: How to store query results in an array?

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

ricardd@xxxxxxxxxxxxxxx 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: ricardd@xxxxxxxxxxxxxxx [mailto:ricardd@xxxxxxxxxxxxxxx]
> Sent: Wednesday, May 25, 2005 11:40 AM
> To: dbi-users@xxxxxxxx
> 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!