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: The Microsoft Jet database engine does not recognize...
    ... In the first query, ... Choose Parameters on the Query menu. ... Access opens a dialog. ... (tblGeoLoc INNER JOIN (tblLocPicker INNER ...
    (microsoft.public.access.queries)
  • 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: Repost - help to merge 2 queries
    ... this is what the final query looks like: ... The restriction on naming is due to the fact that Access SQL won't allow ... I want to run a single query that has inner joins across 2 tables:2 fields ... The first query pulls back the data I need: ...
    (microsoft.public.access.queries)
  • 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)