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 <smokinn@gmail.com> 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 <romsok.tech@gmail.com> 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 <smokinn@gmail.com>
> 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 <romsok.tech@gmail.com>
>> 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