DB2, When Trying To Calculate Difference Between Provided And Stored Timestamp I Get An Error 'The Invocation Of Function Is Ambiquious'

This is my sql string from which I prepare statement: SELECT (DAYS(?) - DAYS(FROM)) * 86400 + (MIDNIGHT_SECONDS(?) - MIDNIGHT_SECONDS(FROM)) AS FROM_DIFF, (DAYS(?

Solution 1:

There are several overloaded versions of the DAYS() function, accepting parameters with different data types: DATE, TIMESTAMP, and VARCHAR. When you use an untyped parameter marker (DAYS(?)) the query compiler is unable to determine which version of the function to use in the query.

You can specify the parameter data type explicitly for compilation: DAYS(CAST(? AS TIMESTAMP)). Alternatively, if you are using a recent DB2 for LUW version (9.7 and higher) you can set the DB2 registry variable:

db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES

to tell the compiler that it should defer the PREPARE call until the query execution time, when parameter data types are already known.