IQ is rounding arithmetic operations

[Former Member]

hi everybody,

The problem that I have is that IQ is rounding any arithmetic division, por example if I execute the query:

SELECT 100/123;

It returns 0.

Any division is returning justo 1 or 0.

What is happening? Is there any option to enable (or diseable) to obtain the correct value?

Thank you.!!!

BTW: I'm using this version:

SAP IQ/16.0.0.807/140804/P/sp08.03/Enterprise

[former_member333181]

This is integer division, so it will return integer. Try   select 100.0/123   which is floating point (double)  division

Regards

Shashi

[Former Member]

Thank you for the response.

The problem is that in our data warehouse we use integers datatype. So if I want to divide integers values, I have to multiply by a decimal value?

For example:

SELECT campo1*1.00001/campo2 FROM FACT_TABLE;

???

[markmumy]

So, two things.  In your first example you left off the FROM clause.  That causes the work to be done in the SA engine, not IQ, so your answers could vary.

Second, you could cast, too, like this:

     select convert( double, campo1 ) / convert( double, campo2 )   FROM table_name_here

If double is not precise enough then use numeric or decimal with scale and precision defined.

Mark

[tayeb_hadjou]

Yes, multiply by 1.0 is another way to force decimal division.

select 1.0 * campo1/campo2 from FACT_TABLE;

Eg.

Select 1.0 * 100/123 from iq_dummy ;

Result : 0.8130

By the way,  this blog may help

http://scn.sap.com/community/database/blog/2014/12/04/sql-conversion-by-exodus-examples-2

Regards,

Tayeb.