KNOWLEDGE BASE

Error "The data types of the operands for the operation "" are not compatible or comparable" When UsingRelative Date Filters in IBM DB2 Views


Published: 10 Jun 2019
Last Modified Date: 11 Jun 2019

Issue

After an upgrade to Tableau Desktop/Server 2018.2 or higher, views that connect to DB2 using relative date filters may experience the following error:

"[IBM][CLI Driver][DB2] SQL0401N The data types of the operands for the operation "" are not compatible or comparable. SQLSTATE=42818"

Such behavior can occur during:

  • Data refreshes for workbooks with DB2 connections created prior to 2018.2
  • Workbook creation/editing when adding a relative date filter into a view using DB2 connection in 2018.2 or later

Environment

  • Tableaus Desktop and Server 2018.2, 2018.3, 2019.1
  • DB2

Resolution

Ensure the data type of the field used for the Relative Date filter matches the data type in DB2 by changing the data type for the field from "DATE" to "DATETIME".

Cause

This behavior is by design:
DB2 connections in Tableau versions 2018.1 and earlier allowed DB2 to pass queries using a native TIMESTAMP_ISO() data type, which caused performance degradations. The queries were adjusted to use TIME() or DATE() with new versions, which can cause conflict sending any queries that use comparisons back to the DB2 database.
Changing the original TIMESTAMP_ISO field to DATETIME() instead of DATE or TIME will allow it to function the same for queries sent to DB2, instead of the data type being treated as something separate and as a result causing errors.
Did this article resolve the issue?