如何從 db2 中的準備好的語句參數中減去天數？

我有一個使用 OLEDB 連接到 DB2 數據庫的 C# 應用程序。基本上，我想這樣做：

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
   SUM(CALC.SUBTOTAL) AS REVENUE 
FROM (
       SELECT 
           CASE
               WHEN O.DATE < ? - 21 DAYS THEN 1
               WHEN O.DATE >= ? - 21 DAYS AND O.DATE < ? - 14 DAYS THEN 2
               WHEN O.DATE >= ? - 14 DAYS AND O.DATE < ? - 7 DAYS THEN 3
               WHEN O.DATE >= ? - 7 DAYS THEN 4
           END AS WEEK, 
           O.ID, O.SUBTOTAL
       FROM COMPANY.ORDERS AS O INNER JOIN 
           COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN 
           COMPANY.STORES AS S ON O.STORE_ID = S.ID 
       WHERE O.DATE >= ? - 28 DAYS
           AND O.DATE <= ?
           AND S.NUMBER = ?
  ) AS CALC
GROUP BY CALC.WEEK;

但是，當我執行它時，我得到了這個：

An expression with a datetime value or a labeled duration is not valid.. SQLCODE=-182, SQLSTATE=42816, DRIVER=3.63.108

如果我將其更改為?使用函式包裝參數TIMESTAMP：

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
   SUM(CALC.SUBTOTAL) AS REVENUE 
FROM (
       SELECT 
           CASE
               WHEN O.DATE < TIMESTAMP(?) - 21 DAYS THEN 1
               WHEN O.DATE >= TIMESTAMP(?) - 21 DAYS AND O.DATE < TIMESTAMP(?) - 14 DAYS THEN 2
               WHEN O.DATE >= TIMESTAMP(?) - 14 DAYS AND O.DATE < TIMESTAMP(?) - 7 DAYS THEN 3
               WHEN O.DATE >= TIMESTAMP(?) - 7 DAYS THEN 4
           END AS WEEK, 
           O.ID, O.SUBTOTAL
       FROM COMPANY.ORDERS AS O INNER JOIN 
           COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN 
           COMPANY.STORES AS S ON O.STORE_ID = S.ID 
       WHERE O.DATE >= TIMESTAMP(?) - 28 DAYS
           AND O.DATE <= TIMESTAMP(?)
           AND S.NUMBER = ?
   ) AS CALC
GROUP BY CALC.WEEK

我收到此錯誤：

The invocation of routine "TIMESTAMP" is ambiguous. The argument in position "1" does not have a best fit.. SQLCODE=-245, SQLSTATE=428F5, DRIVER=3.63.108

作為一個額外的問題，除了創建 PL/SQL 函式之外，有沒有辦法對所有時間戳位置使用相同的參數，而不是提供 8 次相同的值？

只是交叉連接位：

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
   SUM(CALC.SUBTOTAL) AS REVENUE 
FROM (
       SELECT 
           CASE
               WHEN O.DATE < the_param - 21 DAYS THEN 1
               WHEN O.DATE >= the_param - 21 DAYS AND O.DATE < the_param - 14 DAYS THEN 2
               WHEN O.DATE >= the_param - 14 DAYS AND O.DATE < the_param - 7 DAYS THEN 3
               WHEN O.DATE >= the_param - 7 DAYS THEN 4
           END AS WEEK, 
           O.ID, O.SUBTOTAL
       FROM COMPANY.ORDERS AS O INNER JOIN 
           COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN 
           COMPANY.STORES AS S ON O.STORE_ID = S.ID CROSS JOIN
           (SELECT ? as the_param FROM SYSIBM.SYSDUMMY1)
       WHERE O.DATE >= the_param - 28 DAYS
           AND O.DATE <= the_param
           AND S.NUMBER = ?
   ) AS CALC
GROUP BY CALC.WEEK

您可能需要給子查詢一個別名——不確定這在 DB2 上是否是強制性的

在將參數值傳遞給 SQL 之前，通常最好在應用程序程式語言中執行一次日期/時間計算，而不是要求 SQL 對每一行執行計算。

引用自：https://dba.stackexchange.com/questions/28132