Re: How to store query results in an array?

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, ricardd@xxxxxxxxxxxxxxx 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
>

.

Relevant Pages

  • 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: 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)
  • Re: Form fields array
    ... I see your point about the array being empty.. ... Dim incDays ... for counter=0 to iDays 'iDays are the count of how many days between ... >> not know how many records the query will pull up. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Arrays in Access.
    ... The real question, Randy, would be "What is the compelling reason for moving ... query, the DMAX and DMIN functions, for example... ... And all a LOT easier than filling and sorting an array ... >> need to a) sort your array (bubble sort would be efficient, ...
    (comp.databases.ms-access)