Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Wow, -1 and no comment. Would be helpful if you could tell me why my question is stupid. I would be happy to request delete if I am not making any sense. –  Lucas Nov 3 '12 at 17:02
    
It looks like you should check the manual for named prepared statement parameters. –  dezso Nov 3 '12 at 17:19
    
no idea about the DV, but the error seems to be saying the parameter is not coming in as timestamp. How are you invoking this statement? (I don't know much about DB2) –  Jack Douglas Nov 3 '12 at 17:23
    
Yeah, i wish. Not available with OLEDB: The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used –  Lucas Nov 3 '12 at 17:24
    
as for supplying the parameter once, just cross join a 1-row dummy table with the param? –  Jack Douglas Nov 3 '12 at 17:24
show 5 more comments

3 Answers 3

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

share|improve this answer
    
Tried this, seems that the type from the CROSS JOIN is not set because i get this error: An expression with a datetime value or a labeled duration is not valid.. SQLCODE=-182, SQLSTATE=42816, DRIVER=3.63.108 –  Lucas Nov 3 '12 at 21:30
    
this is just the cross join bit—you also need to fix the type problem. That is the same error as before. What string are you inputting (and setting the type to TIMESTAMP) when you press F5? Try something like '2008-02-29.20.00.000000'? –  Jack Douglas Nov 3 '12 at 21:43
    
I'm not sure if DB2 has CROSS JOIN. Try replacing that with INNER JOIN (...) ON 1=1 –  ypercube Nov 4 '12 at 9:30
    
@ypercube, it does –  Lucas Nov 5 '12 at 20:31
add comment

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.

share|improve this answer
    
Could you elaborate on this a bit? I'm not sure you're right... –  dezso Nov 3 '12 at 17:13
    
I usually do stuff like that, but in this case, the different weeks don't have meaning outside of the context of this query. Ideally, i would like to do something to calculate the different week ranges 1 time on the database end and reuse, but, short of pl/sql, i cant figure out a way. –  Lucas Nov 3 '12 at 17:17
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.