Parameters in odd places in prepared expressions in Microsoft SQL and JDBC

Question

So, I have the following statement that I execute in Microsoft SQL 2008:

SELECT 'UTC' AS timezone, rel.unique_id AS relay,sns.unique_id AS sensor, dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) AS time, SUM(CONVERT(FLOAT,dat.data)) AS total FROM sensor_data dat LEFT OUTER JOIN data_package pak ON dat.package_id = pak.id LEFT OUTER JOIN relays rel ON pak.relay_id = rel.id LEFT OUTER JOIN sensors sns ON dat.sensor_id = sns.id LEFT OUTER JOIN sensor_types typ ON sns.sensor_type = typ.id WHERE typ.name = 'Volume' AND dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) > ? AND dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) < ? GROUP BY rel.unique_id,sns.unique_id, dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) ORDER BY time,relay,sensor 

If I set the parameters using the jTDS / JDBC driver as follows:

 Parameter 1: 15 Parameter 2: 15 Parameter 3: 15 Parameter 4: 15 Parameter 5: 2011-10-31 20:00:00 Parameter 6: 15 Parameter 7: 15 Parameter 8: 2011-12-29 19:00:00 Parameter 9: 15 Parameter 10: 15 

I get an error message:

 Caused by: java.sql.SQLException: Column 'data_package.rtime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 

If I manually placed 15 seconds in all of these cases? spaces, the query works fine with dates as parameters.

1) Is it necessary to parameterize the value of the interval in any case (in this case 15) or just I need to avoid it and search and replace it before it becomes a prepared statement (and if this is true, what is the best way to avoid this parameter in Scala / Java )

2) Can I repeat the dateadd (datif ()) task three times? I know that I can not refer to the "time" in the WHERE clause, but is there any other way to indicate that somewhere to make it cleaner?

Answer 1

Your choice is as follows:

 SELECT dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) AS time, 

And your group:

 GROUP BY dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) 

When replacing ? to constants these two are identical.

But using unnamed parameters creates a problem. The selection version uses parameters 1 and 2, and the version group uses parameters 9 and 10. And SQL Server is now not so that these parameters are always equal. Therefore, he gives an error.

You can avoid this situation by computing the field in the subquery:

 left join ( select * , dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) as X from data_package ) as pak on dat.package_id = pak.id 

Now you can refer to pak.X in other parts of your request, for example:

 group by pak.X 

Answer 2

Andomar is right that the problem is using the parameters in your GROUP BY, but I think its solution can be overly complex. It’s easier, I’d think, to write:

 SELECT 'UTC' AS timezone, rel.unique_id AS relay,sns.unique_id AS sensor, dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) AS time, SUM(CONVERT(FLOAT,dat.data)) AS total FROM sensor_data dat LEFT OUTER JOIN data_package pak ON dat.package_id = pak.id LEFT OUTER JOIN relays rel ON pak.relay_id = rel.id LEFT OUTER JOIN sensors sns ON dat.sensor_id = sns.id LEFT OUTER JOIN sensor_types typ ON sns.sensor_type = typ.id WHERE typ.name = 'Volume' AND dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) > ? AND dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) < ? GROUP BY rel.unique_id,sns.unique_id, time ORDER BY time,relay,sensor 

(On the other hand, since in your particular case you are using the same expression in WHERE, perhaps the subquery would be better.)