Re: Need help with aggregate functions and queries that return zero records

Hi Ashok,

MJD also found that bug. I've fixed it but not uploaded the new version yet because I'm fixing a couple of other things. You can apply this patch yourself if you need it soon:

Changing line 1189 of SQL/Statement.pm like so fixes it:

- $rows = $g->calc;
+ $rows = $g->calc || [];

Sorry for the problems.

--
Jeff

Ashok Vemuri wrote:
Hi,

I am having a bit of trouble using aggregate functions with DBI::CSV
(version 0.22) and could use some help figuring out why.

I have the following code snippet that queries a CSV file named
"masterdata", located at c:/test/output folder.

============================
use DBI;

my $dbh = DBI->connect("DBI:CSV:f_dir=c:/test/output")
or die "Cannot connect: " . $DBI::errstr();

my $sth = $dbh->prepare("SELECT callingPartyUnicodeLoginUserID as user,
count(duration) as calls, sum(duration) as duration FROM cmasterdata WHERE
dateTimeOrigination > 1201979253 AND callingPartyUnicodeLoginUserID not like
'' group by callingPartyUnicodeLoginUserID order by calls desc")
or die "Cannot prepare: " . $dbh->errstr();

eval {
$sth->execute();
}

if ($@) {
print "error: $@\n";
}

=============================

The execute() statement above fails with the following error message when
there are no matching records in the CSV file.

DBD::CSV::st execute failed: Can't use an undefined value as an ARRAY
reference at C:/MYINST~1/objects/perl/ActivePerl-
5.8.8.822-MSWin32-x86-280952/site/lib/SQL/Statement.pm line 1197, <GEN0>
line 242.
[for Statement "SELECT callingPartyUnicodeLoginUserID as
users,count(duration)as calls,sum(duration) as duration FROM masterdata
WHERE dateTimeOrigination > 1201991649 AND callingPartyUnicodeLoginUserID
not like '' group by callingPartyUnicodeLoginUserID order by calls desc"]


However, the code runs fine (on the same CSV file) if I remove aggregate
functions from the query. For example, the following query is fine:

SELECT callingPartyUnicodeLoginUserID as user, dateTimeOrigination as
datetime FROM cmasterdata WHERE dateTimeOrigination > 1201979253 AND
callingPartyUnicodeLoginUserID not like ''

Is there a known issue with DBD::CSV where trying aggregate functions when
no matching records gives an error, or am I just doing something wrong here?


TIA,
Ashok


.

Relevant Pages