How do i subtract a number of days from a prepared statement parameter in db2? I have a C# application using OLEDB to connect to a DB2 database.  Basically, I want to do this:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE 
FROM (
        SELECT 
            CASE
                WHEN O.DATE &lt; ? - 21 DAYS THEN 1
                WHEN O.DATE &gt;= ? - 21 DAYS AND O.DATE &lt; ? - 14 DAYS THEN 2
                WHEN O.DATE &gt;= ? - 14 DAYS AND O.DATE &lt; ? - 7 DAYS THEN 3
                WHEN O.DATE &gt;= ? - 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 &gt;= ? - 28 DAYS
            AND O.DATE &lt;= ?
            AND S.NUMBER = ?
   ) AS CALC
GROUP BY CALC.WEEK;


However, when I run that, I get this:

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


And if i change it to wrap the ? parameter with a TIMESTAMP function thusly:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE 
FROM (
        SELECT 
            CASE
                WHEN O.DATE &lt; TIMESTAMP(?) - 21 DAYS THEN 1
                WHEN O.DATE &gt;= TIMESTAMP(?) - 21 DAYS AND O.DATE &lt; TIMESTAMP(?) - 14 DAYS THEN 2
                WHEN O.DATE &gt;= TIMESTAMP(?) - 14 DAYS AND O.DATE &lt; TIMESTAMP(?) - 7 DAYS THEN 3
                WHEN O.DATE &gt;= 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 &gt;= TIMESTAMP(?) - 28 DAYS
            AND O.DATE &lt;= TIMESTAMP(?)
            AND S.NUMBER = ?
    ) AS CALC
GROUP BY CALC.WEEK


I get this error:

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


As a bonus question, short of creating a PL/SQL function, is there a way to use the same parameter for all the timestamp locations instead of supplying the same value 8 times?
Just the cross join bit:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE 
FROM (
        SELECT 
            CASE
                WHEN O.DATE &lt; the_param - 21 DAYS THEN 1
                WHEN O.DATE &gt;= the_param - 21 DAYS AND O.DATE &lt; the_param - 14 DAYS THEN 2
                WHEN O.DATE &gt;= the_param - 14 DAYS AND O.DATE &lt; the_param - 7 DAYS THEN 3
                WHEN O.DATE &gt;= 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 &gt;= the_param - 28 DAYS
            AND O.DATE &lt;= the_param
            AND S.NUMBER = ?
    ) AS CALC
GROUP BY CALC.WEEK


you might need to give the subquery an alias—not sure if that is mandatory on DB2