Re: Need help with aggregate functions and queries that return zerorecords

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

• Need help with aggregate functions and queries that return zero records by Ashok Vemuri
• Re: Need help with aggregate functions and queries that return zerorecords by Jeff Zucker
• Re: Need help with aggregate functions and queries that return zero records by Ashok Vemuri