Geek Answers Handbook

Does SELECT DISTINCT work with Perl DBD :: CSV?

I found a SELECT example on the web. When I try in my script, I get this error message:

Specifying DISTINCT when using aggregate functions isn't reasonable - ignored. at /usr/lib/perl5/site_perl/5.10.0/SQL/Parser.pm line 496.

 #!/usr/bin/perl use warnings; use strict; use DBI; my $dbh = DBI->connect( "DBI:CSV:", undef, undef, { RaiseError => 1, AutoCommit => 1 } ); my $table = 'artikel'; my $array_ref = [ [ 'a_nr', 'a_name', 'a_preis' ], [ 12, 'Oberhemd', 39.80, ], [ 22, 'Mantel', 360.00, ], [ 11, 'Oberhemd', 44.20, ], [ 13, 'Hose', 119.50, ], ]; $dbh->do( "CREATE TEMP TABLE $table AS IMPORT(?)", {}, $array_ref ); my $sth = $dbh->prepare( "SELECT DISTINCT a_name FROM $table" ); $sth->execute(); $sth->dump_results(); $dbh->disconnect();

Does SELECT DISTINCT work with DBD :: CSV or is something wrong with my script?

change: output signal

'Oberhemd' 'Mantel' 'Oberhemd' 'Hose' 4 rows

I thought it should be

'Oberhemd' 'Mantel' 'Hose' 3 rows

Installed Versions:

Perl : 5.010000 (x86_64-linux-thread-multi) OS : linux (2.6.31) DBI : 1.609 DBD::Sponge : 12.010002 DBD::SQLite : 1.25 DBD::Proxy : 0.2004 DBD::Gofer : 0.011565 DBD::File : 0.37 DBD::ExampleP : 12.010007 DBD::DBM : 0.03 DBD::CSV : 0.26

+4
source share
6 answers

Hey. This is an easily reproducible error. SELECT data_display_mask FROM test.csv returns 200 plus rows. SELECT DISTINCT data_display_mask FROM test.csv returns a warning of the same 200 lines.

If I do awk, sort -u for unique (string values), I get 36 values, which I would expect.

Of course, a mistake in the code.

-Kanwar

perl -V Summary of my version of perl5 (version 5 of version 10 subversion 0): Platform: osname = linux, osvers = 2.2.24-6.2.3, archname = i686-linux-thread-multi

DBD :: CSV 0.26 SQL :: Parser 1.23 DBI 1.609

Example:

Setting DISTINCT when using aggregate functions is not reasonable - it is ignored. on the page / opt / perl 2exe / perl5 / lib / site_perl / 5.10.0 / SQL / Parser.pm, line 496. 87060 87060 87060 87060

SQL used SELECT DISTINCT entry_id FROM test.csv

+3
source

Please note that reporting something unreasonable is

  • Just a warning. However, your script is working.
  • Complexity and insensitivity: you do not use any aggregated functions.

I feel an error in DBD::CSV or SQL::Statement .

Edit: DISTINCT explicitly allowed in SQL::Statement

+3
source 
 my $sth = $dbh->prepare("SELECT DISTINCT $attributeName1, COUNT( $attributeName2) FROM tableName GROUP BY $attributeName1, $attributeName2");

this gave me: attributeName1 and a separate attribute accountName2

+1
source

I met the same problem.

You can work around this problem by using the GROUP BY statement instead of DISTINCT .

This is just a twist awaiting error resolution ...

0
source

This is an example of a more general phenomenon with DBD::CSV , namely that it allows a lot of SQL syntax for which the value is silently ignored.

I saw SELECT DISTINCT cases that actually filter out duplicates, so the case you mention here seems to be a mistake, but I haven't found a way to make DISTINCT in SELECT COUNT(DISTINCT foo) FROM bar to do something.

0
source

It works for me. I get 3 rows back.

  $ perl x.pl 'Oberhemd' 'Mantel' 'Hose' 3 rows perl -MDBI -le 'DBI->installed_versions;' Perl : 5.010001 (i686-linux-gnu-thread-multi) OS : linux (2.6.24-28-server) DBI : 1.617 DBD::mysql : 4.020 DBD::Sys : 0.102 DBD::Sponge : 12.010002 DBD::SQLite : 1.33 DBD::Proxy : 0.2004 DBD::Pg : 2.17.2 DBD::Oracle : 1.38 DBD::ODBC : 1.33 DBD::Multiplex : 2.014122 DBD::Gofer : 0.015057 DBD::File : 0.40 DBD::ExampleP : 12.014310 DBD::DBM : 0.06 DBD::CSV : 0.30

Added:

 perl -MSQL::Statement -le 'print $SQL::Statement::VERSION' 1.31

Version 1.23, November 20, 2009 * Proper processing of DISTINCT in aggregate functions

0
source

More articles:


All Articles