RE: How to store query results in an array?

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



.

Relevant Pages

  • Re: Copying a table to an array
    ... You probably don't need a query, ... Dim rstRSet As Recordset ... table to enable data from the latter to be read into an array with GetRows. ... exercise has therefore to be repeated prior to each interrogation. ...
    (microsoft.public.access.formscoding)
  • Re: Mix/Max/Avg Help based on dynamic ranges
    ... Did you enter Biff's formula as an array? ... The query I gave you is dependent on your breaking out the ranges as Biff ... Where Nums are the values to avg based on the head count range. ... deemed optimal for building device profiles based on locn size and device ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Copying a table to an array
    ... "Peter Hibbs" wrote: ... You probably don't need a query, ... Dim rstRSet As Recordset ... table to enable data from the latter to be read into an array with GetRows. ...
    (microsoft.public.access.formscoding)
  • Re: Imitating Mail Merge on server
    ... given moment we know which data (in this case, customers) is being worked ... and concantenate each query together. ... loop the array, plug in the current recordset value. ... We have close to fifty tokens that we use in the application correspondence, ...
    (microsoft.public.inetserver.asp.general)
  • Re: Subquery Confusion
    ... Then I got this crazy idea that an Array can only contain a maximum ... number of characters, ... Then I decide that maybe I'm completely wrong with my query, ... it out of Excel VBA and spit it into Microsoft SQL Server Management ...
    (microsoft.public.excel.programming)