I seem to have hit a bug (I just reported it) and now I'm using an
ugly hack to get around it but I was wondering if there's a better way
to avoid division by zero errors.
The bug is that MonetDB seems to evaluate both branches of a CASE
regardless of whether the branch will be taken or not:
sql>SELECT
more>CASE SUM(payout_units_cpc)
more>WHEN 0 THEN 0
more>ELSE 1
more>END AS avg_cost_cpc
more>FROM daily.stats_2009_week_31;
SELECT
+--------------+
| avg_cost_cpc |
+==============+
| 0 |
+--------------+
1 tuple
Timer 27.990 msec 1 rows
sql>SELECT
more>CASE SUM(payout_units_cpc)
more>WHEN 0 THEN 0
more>ELSE cast(SUM(payout_units_cpc * payout_cpc) as numeric(12,4)) /
SUM(payout_units_cpc)
more>END AS avg_cost_cpc
more>FROM daily.stats_2009_week_31;
!MALException:calc./:Illegal argument Division by zero
0 tuples
Timer 16.415 msec 0 rows
So what I did was I changed my query to:
SELECT
CASE SUM(payout_units_cpc)
WHEN 0 THEN 0
ELSE cast(SUM(payout_units_cpc * payout_cpc) as numeric(12,4)) /
(SUM(payout_units_cpc) + 0.000001)
END AS avg_cost_cpc
FROM daily.stats_2009_week_31 WHERE date_added >= 1249099200 AND
date_added <= 1249185599
Because of the decimal precision it won't affect my numbers but it feels wrong.
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.
http://p.sf.net/sfu/bobj-july_______________________________________________
MonetDB-users mailing list
MonetDB-users@...
https://lists.sourceforge.net/lists/listinfo/monetdb-users
