November 2009 - users-list

[MonetDB-users] limit the number of rows in the result set
by Roman Sokolyuk 06 Nov '09

06 Nov '09

Hi, Could someone please tell me what the syntax is for selecting only say the top 20 rows ?

2 1

[MonetDB-users] Best way to avoid division by zero?
by Guillaume Theoret 06 Nov '09

06 Nov '09

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.

1 0

[MonetDB-users] substring extraction
by Roman Sokolyuk 06 Nov '09

06 Nov '09

Hi, If I have a column that contains email addresses, is there a way for me to select just the domain from SQL, so for dave(a)gmail.com, bob(a)yahoo.com, I will get just "gmail.com", and "yahoo.com"?

2 4

[MonetDB-users] Mathematical functions
by Roman Sokolyuk 05 Nov '09

05 Nov '09

Hi, I am encountering the following issue: SELECT field1, //returns 4 field2, //returns 1 ( ( field2 / field1) * 100) //this returns zero - not what I want - I am looking for 25 Am I doing something wrong with type conversion (integer/floating point)?

3 7

[MonetDB-users] Where is JAR file for monetdb Driver?
by Nafees Ur Rehman 05 Nov '09

05 Nov '09

Hi, I want to configure DBVisualizer with monetdb/sql. When I register for a driver, I can't find the driver files. I follow the instruction listed on the monetdb webpages. Please note that I am using WindowsXP. I would appreciate a kind response. Regards Nafees Ur Rehman __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

2 1

[MonetDB-users] Group By bug?
by Guillaume Theoret 04 Nov '09

04 Nov '09

I think this is a bug in Group By, I would just like to confirm before I submit a bug report. sql>SELECT payout_type, count(*) FROM test.aggregated_detail_data_2009_week_33 stats WHERE date_added >= 1250222400 AND date_added < 1250308799 GROUP BY payout_type; SELECT payout_type, count(*) FROM test.aggregated_detail_data_2009_week_33 stats WHERE date_added >= 1250222400 AND date_added < 1250308799 GROUP BY payout_type; +-------------+-------+ | payout_type | L206 | +=============+=======+ | 1 | 99021 | | 5 | 8719 | | 4 | 8940 | | 2 | 72 | | 3 | 39 | +-------------+-------+ 5 tuples Timer 13.464 msec 5 rows sql>SELECT 0 as breakdown_id, payout_type, count(*) FROM test.aggregated_detail_data_2009_week_33 stats WHERE date_added >= 1250222400 AND date_added < 1250308799 GROUP BY breakdown_id, payout_type; !TypeException:user.s2_1[6]:'group.new' undefined in: (ext14:any, grp12:any) := group.new(_11:bte) !TypeException:user.s2_1[33]:'group.derive' undefined in: (ext52:any, grp50:any) := group.derive(ext14:any, _14:any, _48:bat[:oid,:int]) !TypeException:user.s2_1[35]:'bat.mirror' undefined in: _53:any := bat.mirror(ext52:any) !TypeException:user.s2_1[36]:'algebra.join' undefined in: _54:any := algebra.join(_53:any, _48:bat[:oid,:int]) !TypeException:user.s2_1[39]:'aggr.count' undefined in: _57:bat[:any,:wrd] := aggr.count(_56:bat[:oid,:any], _52:any, _53:any) !SQLException:SQLengine:Program contains errors 0 tuples Timer 1.234 msec 0 rows

1 0

[MonetDB-users] COUNT on varchar column, counts nulls as well - how can I prevent that?
by Roman Sokolyuk 02 Nov '09

02 Nov '09

Hi, I am trying to run AGGR functions on a table - COUNT (column_name) and it looks like it counts <null>'s as well. Is there a way to prevent it?

4 3