topic Decimal number explodes in QlikView

Decimal number explodes

Thu, 26 Jul 2012 08:57:58 GMT

In our database we have a table with fields defined with lots of 16 decimals.

When loading this table in Qlikview the values get multiplied by 10.000.000.000.000.000, so a value of 5,4 becomes 54000000000000000,0000000000000000 ( I'm Dutch so a comma as decimal separator)

I can fully reproduce this behaviour: I created a table myself with just a key and a decimal value like this:

CREATE TABLE [DecTest](
          [id] [int] NOT NULL,
          [decval] [decimal](26, 16) NULL
)

And added some random numbers to the table:

id	decval
1	5,4
2	0.005
3	1.0001
4	0
5	16.543

When loaded these values wil be displayed in QV as:

10001000000000000,0000000000000000	3
10001000000000000,0000000000000000	4
54000000000000000,0000000000000000	1
54000000000000000,0000000000000000	2
165430000000000000,0000000000000000	5

I tried changing the DecimalSep settings but with no change (as expected, I believe these settings only affect display formatting and reading values from text files, not actual numbers from databases). When I change the query to retrieve the result with less decimals

I also ruled out the OLE DB driver, since the problem occurs on both Sybase 12 and MS SQL 2005.

I use QV 10 SR3 64bits

Thanks for any suggestions!

Re: Decimal number explodes

jonathandienst — Thu, 26 Jul 2012 09:09:15 GMT

I think that SQL stores a decimal as a scaled large integer, so in SQL the value is multiplied by 10^16 when you use decimal(26,16) as the type, and it is rendered as a decimal value.

It seems that either QV or the ODBC/OLE providers are ignoring the scaling and simply returning the large integer value. Is decimal a non-Ansi type?

The workaround is to divide the values by 1E16 at load, but I would agree that this should be handled automatically.

Regards

Jonathan

Re: Decimal number explodes

Thu, 26 Jul 2012 09:38:46 GMT

I think I can rule out drivers, since the problem occurs in both MS SQL and Sybase.

However, I just found out that a reload in the 32bit version of QV returns the correct results! I am using the same 32bits drivers in both the 32bits as the 64bit version.

So maybe it's a bug in specific to the 64bit version?