[MonetDB-users] Mathematical functions - users-list - monetdb.org

newer
[MonetDB-users] substring...

[MonetDB-users] Mathematical functions

older
[MonetDB-users] Where is JAR file...

Roman Sokolyuk

4 Nov 2009 4 Nov '09

11:09 p.m.

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

Attachments:

attachment.html (text/html — 292 bytes)

Reply

Sign in to reply online Use email software

Show replies by date

Stefan Manegold

4 Nov 4 Nov

11:45 p.m.

On Wed, Nov 04, 2009 at 06:09:07PM -0500, Roman Sokolyuk wrote:

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

Of what type are your field1 & field2? If they are integer types, the result you get seems to be as expected with integer arithmetics --- unless the SQL standard requires implicit convertion to floating point when processing integer data --- I'm not sure about that. In case integer arithmetics is indeed what the SQL standard requires, you'll need to rewrite your query to explicitly convert the integers to decimals, floats or doubles to force decimal or floating point arithmetics. Alterntatively, you could simply apply arithmetic rules to rewrite the term to, say, ((100 * field2) / field1), obviously risking an overflow in case field2 can be larger than 1/100 of the respective integer type domain. In case the SQL standard strictly requires decimal/loating point arithmetics also with integer types, please file a bug report via our bug tracker on SourceForge. Stefan

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

Reply

Sign in to reply online Use email software

Roman Sokolyuk

11:50 p.m.

Could you please tell me how I can use explicit type conversion from say the output of the COUNT() function, to type REAL? On Wed, Nov 4, 2009 at 6:45 PM, Stefan Manegold wrote:

On Wed, Nov 04, 2009 at 06:09:07PM -0500, Roman Sokolyuk wrote:

...
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)?

Of what type are your field1 & field2? If they are integer types, the result you get seems to be as expected with integer arithmetics --- unless the SQL standard requires implicit convertion to floating point when processing integer data --- I'm not sure about that. In case integer arithmetics is indeed what the SQL standard requires, you'll need to rewrite your query to explicitly convert the integers to decimals, floats or doubles to force decimal or floating point arithmetics. Alterntatively, you could simply apply arithmetic rules to rewrite the term to, say, ((100 * field2) / field1), obviously risking an overflow in case field2 can be larger than 1/100 of the respective integer type domain.

In case the SQL standard strictly requires decimal/loating point arithmetics also with integer types, please file a bug report via our bug tracker on SourceForge.

Stefan

...
------------------------------------------------------------------------------

...
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/http://www.cwi.nl/%7Emanegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Guillaume Theoret

5 Nov 5 Nov

3:40 a.m.

I had this problem today as well. What I did to solve it was this: select cast(field2 as numeric(12,4)) / field1 * 100 This will force monetdb into not simply doing integer division. On Wed, Nov 4, 2009 at 6:09 PM, Roman Sokolyuk wrote:

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

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Roman Sokolyuk

3:02 p.m.

Thanks a lot - this works - could you tell me please what the 12,4 stand for? On Wed, Nov 4, 2009 at 10:40 PM, Guillaume Theoret wrote:

I had this problem today as well. What I did to solve it was this:

select cast(field2 as numeric(12,4)) / field1 * 100

This will force monetdb into not simply doing integer division.

On Wed, Nov 4, 2009 at 6:09 PM, Roman Sokolyuk wrote:

...
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)?

------------------------------------------------------------------------------

...
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Guillaume Theoret

3:53 p.m.

MonetDB's "numeric" basically a decimal type. Mysql has a good explanation: http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html So basically 12,4 means 12 digits, 4 of them to the right of the decimal. So 12345678.1234 is a representable number but if you try more digits on either side they will either be rounded in the case of the decimal side or you will get an overflow error in the case of the left side: sql>select cast(12345678.1234 as decimal(12,4)); select cast(12345678.1234 as decimal(12,4)); +----------------+ | single_value | +================+ | 12345678.1234 | +----------------+ 1 tuple Timer 0.400 msec 1 rows sql>select cast(12345678.12349 as decimal(12,4)); select cast(12345678.12349 as decimal(12,4)); +----------------+ | single_value | +================+ | 12345678.1235 | +----------------+ 1 tuple Timer 0.422 msec 1 rows sql>select cast(123456789.1234 as decimal(12,4)); !SQLException:convert:too many digits (13 > 12) 0 tuples Timer 0.426 msec 0 rows On Thu, Nov 5, 2009 at 10:02 AM, Roman Sokolyuk wrote:

Thanks a lot - this works - could you tell me please what the 12,4 stand for?

On Wed, Nov 4, 2009 at 10:40 PM, Guillaume Theoret wrote:

...
I had this problem today as well. What I did to solve it was this:

select cast(field2 as numeric(12,4)) / field1 * 100

This will force monetdb into not simply doing integer division.

On Wed, Nov 4, 2009 at 6:09 PM, Roman Sokolyuk wrote:

...
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)?

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Roman Sokolyuk

4:26 p.m.

AND for some reason when I apply the cast like this: (CAST(field1 AS NUMERIC(12,4))/CAST(field2 AS NUMERIC(12,4))) AS ratio the result is 0.2 when field1=3 and field2=5 - shouldn't the result be 0.6? On Thu, Nov 5, 2009 at 10:53 AM, Guillaume Theoret wrote:

MonetDB's "numeric" basically a decimal type.

Mysql has a good explanation:

http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

So basically 12,4 means 12 digits, 4 of them to the right of the decimal.

So 12345678.1234 is a representable number but if you try more digits on either side they will either be rounded in the case of the decimal side or you will get an overflow error in the case of the left side:

sql>select cast(12345678.1234 as decimal(12,4)); select cast(12345678.1234 as decimal(12,4)); +----------------+ | single_value | +================+ | 12345678.1234 | +----------------+ 1 tuple Timer 0.400 msec 1 rows sql>select cast(12345678.12349 as decimal(12,4)); select cast(12345678.12349 as decimal(12,4)); +----------------+ | single_value | +================+ | 12345678.1235 | +----------------+ 1 tuple Timer 0.422 msec 1 rows sql>select cast(123456789.1234 as decimal(12,4)); !SQLException:convert:too many digits (13 > 12) 0 tuples Timer 0.426 msec 0 rows

On Thu, Nov 5, 2009 at 10:02 AM, Roman Sokolyuk wrote:

...
Thanks a lot - this works - could you tell me please what the 12,4 stand for?

On Wed, Nov 4, 2009 at 10:40 PM, Guillaume Theoret wrote:

...
I had this problem today as well. What I did to solve it was this:

select cast(field2 as numeric(12,4)) / field1 * 100

This will force monetdb into not simply doing integer division.

On Wed, Nov 4, 2009 at 6:09 PM, Roman Sokolyuk wrote:

...
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)?

...
...
...
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...
...
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------

...
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Guillaume Theoret

5:10 p.m.

Are you sure your fields are set correctly? sql>create schema test; Operation successful Timer 3410.692 msec 1 rows sql>create table test.test (field1 int not null, field2 int not null); Operation successful Timer 4343.730 msec 1 rows sql>insert into test.test (field1, field2) values (3,5); Rows affected 1 Timer 1076.233 msec 0 rows sql>select (CAST(field1 AS NUMERIC(12,4))/CAST(field2 AS NUMERIC(12,4))) AS ratio from test.test; select (CAST(field1 AS NUMERIC(12,4))/CAST(field2 AS NUMERIC(12,4))) AS ratio from test.test; +--------------------+ | ratio | +====================+ | 0.6000 | +--------------------+ 1 tuple Timer 671.128 msec 1 rows On Thu, Nov 5, 2009 at 11:26 AM, Roman Sokolyuk wrote:

AND for some reason when I apply the cast like this: (CAST(field1 AS NUMERIC(12,4))/CAST(field2 AS NUMERIC(12,4))) AS ratio

the result is 0.2 when field1=3 and field2=5 - shouldn't the result be 0.6?

On Thu, Nov 5, 2009 at 10:53 AM, Guillaume Theoret wrote:

...
MonetDB's "numeric" basically a decimal type.

Mysql has a good explanation:

http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

So basically 12,4 means 12 digits, 4 of them to the right of the decimal.

So 12345678.1234 is a representable number but if you try more digits on either side they will either be rounded in the case of the decimal side or you will get an overflow error in the case of the left side:

sql>select cast(12345678.1234 as decimal(12,4)); select cast(12345678.1234 as decimal(12,4)); +----------------+ | single_value | +================+ | 12345678.1234 | +----------------+ 1 tuple Timer 0.400 msec 1 rows sql>select cast(12345678.12349 as decimal(12,4)); select cast(12345678.12349 as decimal(12,4)); +----------------+ | single_value | +================+ | 12345678.1235 | +----------------+ 1 tuple Timer 0.422 msec 1 rows sql>select cast(123456789.1234 as decimal(12,4)); !SQLException:convert:too many digits (13 > 12) 0 tuples Timer 0.426 msec 0 rows

On Thu, Nov 5, 2009 at 10:02 AM, Roman Sokolyuk wrote:

...
Thanks a lot - this works - could you tell me please what the 12,4 stand for?

On Wed, Nov 4, 2009 at 10:40 PM, Guillaume Theoret wrote:

...
I had this problem today as well. What I did to solve it was this:

select cast(field2 as numeric(12,4)) / field1 * 100

This will force monetdb into not simply doing integer division.

On Wed, Nov 4, 2009 at 6:09 PM, Roman Sokolyuk wrote:

...
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)?

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

5536

Age (days ago)

5537

Last active (days ago)

Download

7 comments

3 participants

tags

participants (3)

Guillaume Theoret
Roman Sokolyuk
Stefan Manegold