Wednesday, March 6, 2013

QlikView Query OLEDB error on Query that runs against Oracle in other products

Quick Description:  A query valid in products like Toad/SQLPlus blows up with a non-descriptive OLEDB error in QlikView
Using the fake query below as an example:
QVTable:
Select
table1.field1,
table1.field2,
table1.field3,
table1.field4,
table2.field5,
table2.field6,
table3.field2,
table3.field7
from
table1 join table2 on table1.key1 = table2.key1
join talble3 on table2.key2 = table2.key2;
The culprit is that QlikView appears to ignore the table specification and sees table1.field2 and table3.field2 as ambiguous, causing an OLEDB error with no further information.   It makes sense considering the fact that a load statement doesn't need to qualify tables from a select statement with joins and would therefore not have a way of knowing which field was being loaded.  It still threw me for a loop troubleshooting a generated query with a pretty long list of joined fields.
Aliasing the fields as below fixes the problem.
QVTable:
Select
table1.field1,
table1.field2 as table1_field2,
table1.field3,
table1.field4,
table2.field5,
table2.field6,
table3.field2 as table3_field2,
table3.field7
from
table1 join table2 on table1.key1 = table2.key1
join talble3 on table2.key2 = table2.key2;

One other thing that can cause an uninformative OLEDB error on a query that runs in other products is a date field that Oracle doesn't like by the time QlikView has interpreted it.  I'm not aware of a way to force NLS_LANG from a QlikView script, but forcing it to char then to date has, in my experience, "fixed" this issue.  For example:
SELECT CURRENT_DATE FROM DUAL
could need to be formatted as
SELECT TO_DATE(TO_CHAR(CURRENT_DATE, 'MM-DD-YYYY'), 'MM-DD-YYYY') FROM DUAL
I'm not proud of some of the things I've done to get dates from Oracle to QlikView

No comments:

Post a Comment