RE: How to store query results in an array?

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
>
>
>


.

Relevant Pages

  • Re: Database-Query and AutoFill??????
    ... first query, so it gets data from both source tables. ... rightmost one - leave no gaps) with formula, which will retrieve according ... In data range properties for first query, ... > values of coloumn 1 and 2. ...
    (microsoft.public.excel.misc)
  • Re: Pass a parameter to nested query
    ... current month and year showing a list of employees as row headings and the ... The first query puts together the ... This is the query where I want to pick up the month's worth of days as I ... FROM qryEmplEvent ...
    (microsoft.public.access.queries)
  • RE: How to store query results in an array?
    ... How to store query results in an array? ... out how to avoid extracting and formatting each row from the first query ...
    (perl.dbi.users)
  • RE: Need advice on speeding query up
    ... Month functions actually make an index on that column worthless plus have to ... into the next (a Crosstab query), and then the second query is fed to a ... The first query is called qryCustomerAggregate, ...
    (microsoft.public.access.queries)
  • Re: Are Linq-SQL methods commutative
    ... Take make no sense without ordering, LINQ to SQL will create an order ... The first query: ... Will return the ordered set, while the second query: ... The reason for this is that in the first query, ...
    (microsoft.public.dotnet.languages.csharp)