Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re^4: Combine data in table

by DrAxeman (Beadle)
on Aug 07, 2005 at 19:10 UTC ( #481726=note: print w/ replies, xml ) Need Help??


in reply to Re^3: Combine data in table
in thread Combine data in table

I got the Perl loop method to work (mostly for the experience). But now I'm working on the SQL method. My code looks like:

# Connect to the database, (the directory containing our csv file(s)) my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;"); # Associate our csv file with the table name 'results' $dbh->{'csv_tables'}->{'results'} = { 'file' => "psinfooutputdiskfile. +csv"}; my @rows = $dbh->selectrow_arrayref("SELECT IP, SUM(PartitionSize), SU +M(PartitionFree) FROM results GROUP BY IP"); print ("@rows\n"); # Just here to view output

I'm getting

SQL ERROR: Can't find table names in FROM clause! Execution ERROR: No command found!.

Not sure what is wrong here.

it I modify the SELECT statement to:
my @rows = $dbh->selectrow_arrayref("SELECT IP FROM results");

It works. But if I try to include a GROUP BY or SUM I get the error.


Comment on Re^4: Combine data in table
Select or Download Code
Re^5: Combine data in table
by davidrw (Prior) on Aug 07, 2005 at 19:30 UTC
    Note first that you probably want my $rows = $dbh->selectall_arrayref( ... ) instead of selectrow_arrayref()

    are those column names right? should they be "colIP", "colPartitionSize", "colPartitionFree" instead?

    Besides that, the SQL looks fine .. do the statements "SELECT IP FROM results" and "SELECT IP FROM results GROUP BY IP" and "SELECT SUM(PartitionSize), SUM(PartitionFree) FROM results" work?
      #!/usr/bin/perl use strict; use warnings; use DBI; use Data::Dumper; # Connect to the database, (the directory containing our csv file(s)) my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;"); # Associate our csv file with the table name 'results' $dbh->{'csv_tables'}->{'results'} = { 'file' => "psinfooutputdiskfile. +csv"}; my @rows = $dbh->selectall_arrayref("SELECT IP FROM results GROUP BY I +P"); print Dumper @rows;
      Fails. If I take the GROUP BY off, everything is OK. Same with the SUM statements.

      "SELECT IP FROM results" works
      "SELECT IP FROM results GROUP BY IP" and "SELECT SUM(PartitionSize), SUM(PartitionFree) FROM results" fail.
        doh. after all this, 'GROUP BY' isn't supported by DBD::CSV ... following is from the cb:
        davidrw 2005-08-07 16:03:24-04 jZed (or anyone) did you see 481726 and replies? Any reason that a + "GROUP BY" sql statement wouldn't work w/DBD::CSV ? bart 2005-08-07 16:05:53-04 Because it didn't get implemented? castaway 2005-08-07 16:06:22-04 always a good reason ;) davidrw 2005-08-07 16:08:10-04 not implemented? doh! is that a guess or a fact? davidrw 2005-08-07 16:08:59-04 davidrw goes to skim DBD::CSV docs for known bugs/limitations davidrw 2005-08-07 16:10:24-04 davidrw doesn't find anything obvious in DBD::CSV or DBD::File pod Zaxo 2005-08-07 16:12:43-04 since there isn't a db daemon managing things, constructs like tha +t have to be written from scratch in DBD::CSV castaway 2005-08-07 16:13:25-04 right Corion 2005-08-07 16:13:41-04 Hmmm. "Group By" would likely be a hash filter sitting between the + row-scanner and the aggregator function(s) Corion 2005-08-07 16:14:03-04 ... so it "shouldn't be hard". But then, what do I know about DBDs + :) davidrw 2005-08-07 16:14:24-04 hmm.. true. davidrw 2005-08-07 16:15:15-04 i wonder if any other non-db-daemon DBD:: modules implement GROUP +BY? Zaxo 2005-08-07 16:16:32-04 Berkeley might, but I don't know Corion 2005-08-07 16:17:55-04 davidrw: SQLite does. :) davidrw 2005-08-07 16:18:42-04 ah. y, i knew that -- i use it a lot :) davidrw 2005-08-07 16:18:52-04 it's been a slow lazy sunday ...

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://481726]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (11)
As of 2014-12-19 08:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (74 votes), past polls