DB2，当尝试计算提供的和存储的时间戳之间的差异时，我得到一个错误“函数的调用是荒谬的” [英] DB2, when trying to calculate difference between provided and stored timestamp I get an error 'The invocation of function is ambiquious'

问题描述

这是我准备语句的sql字符串：

This is my sql string from which I prepare statement:

SELECT (DAYS(?) - DAYS(FROM)) * 86400 + (MIDNIGHT_SECONDS(?) -
           MIDNIGHT_SECONDS(FROM)) AS FROM_DIFF,
       (DAYS(?) - DAYS(TO)) * 86400 + (MIDNIGHT_SECONDS(?) - 
           MIDNIGHT_SECONDS(TO)) AS TO_DIFF
FROM CALENDAR.EVENTS WHERE ID = ?

使用以下代码填充值：

        ps.setTimestamp(1, new Timestamp(...));
        ps.setTimestamp(2, new Timestamp(...));
        ps.setTimestamp(3, new Timestamp(...));
        ps.setTimestamp(4, new Timestamp(...));
        ps.setInt(5, ...);

并获得例外：

com.ibm.db2.jcc.am.SqlSyntaxErrorException：DB2 SQL错误：
SQLCODE = -245，SQLSTATE = 428F5，SQLERRMC = DAYS; 1，DRIVER = 4.16.53

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-245, SQLSTATE=428F5, SQLERRMC=DAYS;1, DRIVER=4.16.53

当我直接从SQL浏览器运行它时，它运行完美：

When I run it directly from SQL browser it run flawlessly:

SELECT 
    (DAYS('2015-05-05 00:00:00.0') - DAYS(FROM)) * 86400 + (MIDNIGHT_SECONDS('2015-05-05 00:00:00.0') - MIDNIGHT_SECONDS(FROM)) AS FROM_DIFF,
    (DAYS('2015-05-05 00:00:00.0') - DAYS(TO)) * 86400 + (MIDNIGHT_SECONDS('2015-05-05 00:00:00.0') - MIDNIGHT_SECONDS(TO)) AS TO_DIFF  
FROM CALENDAR.EVENTS WHERE ID = 1055;

错误在哪里？

谢谢你。

PS公式如何计算时差取自本文：
DB2基础知识：有趣的日期和时间

P.S. Formula how to calculate time difference is taken from this article: DB2 Basics: Fun with Dates and Times

推荐答案

有几个重载版本的 DAYS（）函数，接受不同数据类型的参数： DATE ， TIMESTAMP 和 VARCHAR 。当您使用无类型参数标记（ DAYS（？））时，查询编译器无法确定查询中要使用的函数的版本。

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.

您可以明确指定参数数据类型进行编译： DAYS（CAST（？AS TIMESTAMP））。或者，如果您使用最近的DB2 for LUW版本（9.7及更高版本），则可以设置DB2注册表变量：

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

告诉编译器它应该推迟PREPARE调用，直到查询执行时间参数数据类型是已知的。

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

这篇关于DB2，当尝试计算提供的和存储的时间戳之间的差异时，我得到一个错误“函数的调用是荒谬的”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！