Using the fake query below as an example:
QVTable: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.
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;
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 DUALcould need to be formatted as
SELECT TO_DATE(TO_CHAR(CURRENT_DATE, 'MM-DD-YYYY'), 'MM-DD-YYYY') FROM DUALI'm not proud of some of the things I've done to get dates from Oracle to QlikView
No comments:
Post a Comment