caused by GatewayException: ResultSet is from UPDATE. No Data.
caused by SQLException: ResultSet is from UPDATE. No Data.
SET @prev_value = -1;
SELECT ABS(SUM(a.curr_value - a.lag_value))
FROM
(
SELECT time_stamp, @prev_value AS lag_value, @prev_value:=value AS curr_value
FROM site_146_data
WHERE tank_ndx = 1
ORDER BY time_stamp
) AS a
WHERE a.curr_value - a.lag_value < 0;
If I take out the SET statement it will run. Does anyone know of a workaround to this?
I have allowMultiQueries set to true and useAffectedRows set to false in the connection properties to the database.
I don’t think you can fix this in a regular binding – I’m pretty sure Ignition is using java.sql.Statement.executeQuery() under the hood. It only accepts one statement and returns one result set. Ignition uses connection pooling, so two separate queries back to back will almost certainly not use the same connection.
The simplest solution is to use a database that directly supports window aggregates and the lag() function. MySQL is pretty much the only major DB that doesn’t.
SELECT ABS(SUM(a.curr_value - a.lag_value))
FROM
(
SELECT time_stamp, @prev_value AS lag_value, @prev_value:=value AS curr_value
FROM site_146_data
WHERE tank_ndx = 1
ORDER BY time_stamp
) AS a, (SELECT @prev_value = -1) AS b
WHERE a.curr_value - a.lag_value < 0;
SELECT ABS(SUM(a.curr_value - a.lag_value)) AS Consumption
FROM (
SELECT time_stamp, @prev_value AS lag_value, @prev_value:=value AS curr_value
FROM site_146_data
WHERE tank_ndx = 1
ORDER BY time_stamp
) AS a, (SELECT @prev_value := -1) AS b
WHERE a.curr_value - a.lag_value < 0
