An incorrect query might be pushed down to the database, which results in an error when the following are true:

• You query a database table where you want to call a database function that compares two dates, datetimes, or time variables to determine an interval between the two values.
• You created a SAS function using the FCMP procedure that calculates the intervals between two dates, datetimes, or time variables.
• You updated the list of functions (via the SQL_FUNCTIONS = LIBNAME option) that are to be considered for push down to include the new function that you just created.
• The CMPLIB = system option has been set to one or more SAS data sets (default is blank).
• Your query does not include one of the following implicit-passthrough triggers:
  • DISTINCT operator
  • ORDER BY clause
  • GROUP BY clause
  • HAVING clause
  • the Summary function
  • the join of two or more tables from the same libref (or different ones that are connecting to the same database, where the only difference between the two LIBNAME statements is the SCHEMA= option)
  • SET operator other than outer union
  • WHERE clause containing a subquery (that references a database table using the same libref)

The error that occurs vary depending on the underlying database that you are connecting to. Some of the errors that occur include the following:

ERROR: CLI open cursor error: ERROR: Error in select view conditions: Unable to execute condition using types 'localdate' and 'long'.java.sql.SQLException: Error in select view conditions: Unable to execute condition using types 'localdate' and 'long'.;Error while executing the query

---

ERROR: CLI prepare error: [SAP AG][LIBODBCHDB SO][HDBODBC] General error;266 inconsistent datatype: DATE type is not comparable with INT type.: line 1 col 94 (at pos 93)

---

ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE. 

Cause

Instead of passing the interval as entered in the code, the interval changes to the data type of the input columns to the function. 

For example, if the query included NumDaysBetween(date1,date2)=1, the 1 would be passed as the equivalent of 02JAN1960. In addition, if date1 and date2 were datetimes, the 1 would be passed as the equivalent of 01JAN1960:00:00:01.

Workaround

The workarounds for this issue include the following:

• Include one of the implicit pass-through triggers.
• Do not set the CMPLIB system option to anything other than the default (blank).