Thursday, January 22, 2009

Error: fetched column value was truncated

I got an error yesterday on a QlikView document that had reloaded dozens of times before. It appeared to be an Oracle SQL error. The error message that appeared in the pop-up box during loading said, SQL Error:[Oracle][ODBC][Ora]ORA-01406:fetched column value was truncated

Despite appearing like a SQL error, the piece of SQL code didn’t cause any errors when submitted through Oracle SQL*Plus. It only caused an error when used to load a table in the QlikView document. I was using QlikView version 8.50.6206.5.

With a little trial-and-error I narrowed down the source of the problem to a single database column, named QTY, which was defined in the Oracle database as a FLOAT. Since this document had reloaded successfully in the past, it must be the new data stored in the table that is causing the problem now.

In any case, I was able to make the document load and satisfy the reporting requirements by simply changing the SQL a little from SQL SELECT QTY… to SQL SELECT ROUND(QTY,2)… After this change, the document reloaded with no problem.

I’m hoping this blog entry might help someone fix the problem if it happens to them. And, inasmuch as this blog also serves as my own QlikView notes it might serve as a reminder to myself when it happens again in one of my documents.

(Note the comment describing another experience with this error)

1 comment:

Anonymous said...

We had a similar issue with Oracle loads in QV 8.2, and used the same mechanism to solve them. We did notice that for each column, we could find a value of round(Column,X) that would return an error where round(Column, X-1) would not return an error, but this value was different for each column and we were not able to locate a pattern. According to our DBA, the issue is related to the ODBC connector, not the database or QlikView.