KB14456: ‘Inconsistent datatypes: expected - got CLOB’ error occurs when trying to execute a report with an LOB column being used against Oracle in MicroStrategy Developer 9.4.x - 10.x

Salesforce Solutions Architect • Strategy

This technical note documents a behavior observed when executing a report with an LOB column against Oracle in MicroStrategy Developer 9.4.x - 10.x

SYMPTOM:
A project contains the following table called TEST_JOSH, which contains only two columns: A, which is the Numeric ID column, and B, which is LongVarChar DESC column. In the Oracle database where this table is contained, the B column is stored as a CLOB (Character Large Object), which Strategy Developer 9.4.x - 10.x maps to the column as a LongVarChar correctly, as shown below:

A developer unchecks the option 'The key specified is the true key for the warehouse table' as shown below, for these columns are not the true keys of the table:

Creating a simple attribute called CLOB_TEST, a user chooses the column A as the ID attribute form and B as the DESC attribute form, as shown below:

When the user tries to execute a simple report with only the attribute on it to test with, the following error occurs:


QueryEngine encountered error. Execute Query failed. Error type. Odbc error. Odbc operation attempted SQLExecDirect ORA-00932: inconsistent datatypes: expected - got CLOB

CAUSE:
An LOB column (this includes CLOB and BLOB datatypes) cannot be specified in a SELECT... DISTINCT or SELECT...UNIQUE statement or in a join. Oracle does not support these types of comparisons with LOB columns.

ACTION:
For some simple SQL examples as the one above, the distinct can be removed by unchecking the 'The key specified is the true key for the warehouse table' option. This option means that if the ID is part of the lowest level of the table and if it is checked, then it does not need to do a distinct, for there will only be one instance of each ID in the table. However, if this is unchecked, then it means it is not at the lowest level and that there may be duplicates, and therefore will need to perform a distinct.

Consequently, to avoid the issue, check this option, which will then deem the DISTINCT unnecessary for the SQL generation and the report will execute correctly, as shown below:

Comment

0 comments

Details

Knowledge Article

Published:

April 25, 2017

Last Updated:

April 25, 2017