How do i subtract a number of days from a prepared statement parameter in db2?

Question

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 < ? - 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;

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 < 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

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?

Answer 1

Just the cross join bit:

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

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

Answer 2

It's generally preferable to perform your date / time calculation once in your application programming language, before you pass the parameter value to SQL, rather than asking SQL to perform the calculations on each row.

Answer 3

Not sure if this helps you out? I just found this function while I was working with some date/timestamp logic.

check out the TIMESTAMPDIFF function.

I needed to create a trigger that deleted data that was older than 30 days so I found this handy. Here is how I used it.

delete from systools.stmg_table 
where TIMESTAMPDIFF(16, CHAR(TIMESTAMP(current timestamp) - stmg_timestamp)) >= 30

The 16 defines I want the return in days. In my case I took the current timestamp and subtracted the timestamp field in question. You do then have to case this to CHAR (can't be bigger than CHAR(22). and then it returns what you are after (in my case the number of days between the two dates, which I then compare to 30 to see if I needed to remove them.

Perhaps this function would be useful in your query above.