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 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
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
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?
