Bug? OLEDB vs ODBC give different numerical results

[racer25]

Hi,

I am getting different results on the same data when I switch between OLEDB or ODBC - ODBC giving the correct answer.

I have tested with Oracle 11g and Oracle 12c

My load script - I simply uncomment which driver I am using.

// ODBC CONNECT32 TO [DB1;DBQ=DB1] (XUserId is UserID, XPassword is Password);

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=UserID;Data Source=DB1;Extended Properties=""] (XPassword is Password);

ICA:

LOAD "ICA_COMPANY",

    "ICA_BRANCH",

    "ICA_INSCODE",

    "ICA_INSBRANCH",

    "ICA_CURRENCYCODE",

    "ICA_TRANIDENT",

    "ICA_INVOICENO",

    "ICA_SEQUENCENO",

    "ICA_SEQUENCETYPE",

    "ICA_NETTPAYT";

SQL SELECT "ICA_COMPANY",

    "ICA_BRANCH",

    "ICA_INSCODE",

    "ICA_INSBRANCH",

    "ICA_CURRENCYCODE",

    "ICA_TRANIDENT",

    "ICA_INVOICENO",

    "ICA_SEQUENCENO",

    "ICA_SEQUENCETYPE",

    "ICA_NETTPAYT"

FROM "OWNER_CDG"."ICA_INSURER_ACCOUNTING";

WIth OLEDB Drivers first row ICA_NETTPAYT is incorrect

ICA_COMPANY	ICA_BRANCH	ICA_INSCODE	ICA_INSBRANCH	ICA_CURRENCYCODE	ICA_TRANIDENT	ICA_INVOICENO	ICA_SEQUENCENO	ICA_SEQUENCETYPE	ICA_NETTPAYT
AON	266	M003	001	EUR	I	00025047	000000	0	30315.6
AON	266	M031	001	EUR	I	00025047	000000	0	792.7101
AON	266	M003	001	EUR	I	00025047	000001	P	-1514.48
AON	266	M031	001	EUR	I	00025047	000001	P	-792.7101

With ODBC the correct answer for ICA_NETTPAYT

ICA_COMPANY	ICA_BRANCH	ICA_INSCODE	ICA_INSBRANCH	ICA_CURRENCYCODE	ICA_TRANIDENT	ICA_INVOICENO	ICA_SEQUENCENO	ICA_SEQUENCETYPE	ICA_NETTPAYT
AON	266	M003	001	EUR	I	00025047	000000	0	1514.48
AON	266	M031	001	EUR	I	00025047	000000	0	792.7101
AON	266	M003	001	EUR	I	00025047	000001	P	-1514.48
AON	266	M031	001	EUR	I	00025047	000001	P	-792.7101

I have tested this on Qlikview 11.2 SR3 with Oracle 11g - running Windows Server 2008 (64 bit) but Oracle Server ( on a different server is 32 bit ).

I have tested this on Qlikview 12.0 SR4 with Oracle 12c - running Window 7 64 bit ( as above for Oracle 32 bit ).

The OLEDB refresh is significantly quicker ( hence preferred ), ODBC is accurate ( obvious requirement ).

I'd appreciate feedback/thoughts on this, is it a bug? Do we just blame Oracle? Works fine when I pull the data into Toad.

Thanks in advance.

Rob

[marcus_sommer]

I think the problem is the OLEDB driver which has a bug respectively couldn't handle this case properly. Maybe there other OLEDB driver for this type of database available.

Beside them it might help if you forced a further processing of the data instead of just pulling them. I mean something like: where 1=1 or a formatting of this field or using a top-statement or something similar ... sometimes it's strange and it worked with them.

- Marcus

[racer25]

Hi Marcus,

Its an Oracle database and an Oracle driver - it should work? I am not overly familiar with other drivers that I could install to replace the Oracle driver ?

Thanks,

Rob

[marcus_sommer]

I don't know if the driver could be replaced with another. Maybe you could find here some useful informations:

Installing Oracle OLEDB Drivers.docx

Connectivity to Oracle Database

- Marcus

[racer25]

In the small world that it is the author of the 2nd document Stephen Redmond actually installed the drivers on our Qlikview Server ( which has the error ). I installed on my laptop and same error.

Its fine for ODBC but not for OLEDB.