Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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.