Skip to main content
Version Pentaho
Hitachi Vantara Lumada and Pentaho Documentation

Force PDI to use DATE instead of TIMESTAMP in Parameterized SQL Queries

  1. Last updated
  2. Save as PDF

If your query optimizer is incorrectly using the predicate TIMESTAMP, it is likely because the JDBC driver/database normally converts the data type from a TIMESTAMP to a DATE. In special circumstances this casting prevents the query optimizer of the database not to use the correct index.

Use a Select Values step and set the Precision to 1 and Value to DATE. This forces the parameter to set as a DATE instead of a TIMESTAMP.

For example, if Oracle says it cannot use the index, and generates an error message that states:

The predicate DATE used at line ID 1 of the execution plan contains an implicit
   data type conversion on indexed column DATE. This implicit data type conversion prevents
   the optimizer from selecting indices on table A.

After changing the Precision to 1, setting the Value as a DATE, the index can be used correctly.