Hi,
Here is a version written in PL/SQL using StepSqlite
(https://www.metatranz.com/stepsqlite).
Perhaps it would be closest to what you expect: note how the variable
kwhcost is directly used in second query and also the StepSqlite
built-in date operators to simplify the query.
1. Compile the code given below to a loadable SQLite extension on
StepSqlite website
2. Create the tables in db manually.
3. Load compiled extension into your DB as follows:
sqlite> select load_extension("libpackage.so");
===
CREATE TABLE monitordata_hourly(deviceaddress integer, ch1kwh float, date date);
CREATE TABLE applications(kwhcost1 float);
DECLARE
kwhcost float;
BEGIN
SELECT kwhcost1 INTO kwhcost FROM applications;
FOR device_rec IN (SELECT monitordata_hourly.deviceaddress addr,
Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
Round(Sum(monitordata_hourly.ch1kwh),3)* kwhcost AS SumOfch1kwh_cost
FROM monitordata_hourly
WHERE monitordata_hourly.deviceaddress=142265 and
monitordata_hourly.date >= (sysdate - 30)
GROUP BY monitordata_hourly.deviceaddress)
LOOP
dbms_output.put_line('Device Addr: ' || device_rec.addr || '
SumOfch1kwh: ' || device_rec.SumOfch1kwh);
END LOOP;
END;
===
regards,
sk
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
