Hi!
I’m exporting the data acquired from a GPS to a PostgreSQL database. On the SQL command window to export the current data, I’m using the following code:
INSERT INTO geodata_temp (datetime, lat, lon, batt, temperature, dist,vel,sat,prec)
VALUES(
@datetime,
case when @cnlNum = 40001 then @val/1000 end,
case when @cnlNum = 40002 then @val/1000 end,
case when @cnlNum = 40003 then @val end,
case when @cnlNum = 40004 then @val end,
case when @cnlNum = 40005 then @val end,
case when @cnlNum = 40006 then @val end,
case when @cnlNum = 40007 then @val end,
case when @cnlNum = 40008 then @val end
);
INSERT INTO geodata (datetime, lat, lon, batt, temperature, dist,vel,sat,prec)
SELECT datetime as "time",
max(lat) as "lat",
max(lon) as "lon",
max(batt) as "batt",
max(temperature) as "temperature",
max(dist) as "dist",
max(vel) as "vel",
max(sat) as "sat",
max(prec) as "prec"
FROM geodata_temp
group by datetime
order by datetime desc;
truncate table geodata_temp;
The first insert results on a diagonal database, like:
time null null null null null null
null lat null null null null null
null null lon null null null null
...
null null null null null null prec
The second insert moves the data from the temporary database (geodata_temp), grouping and sorting the data like:
time lat lon batt ... prec
In the end, It truncates the temporary table.
However, the final result is the same as the first insert result.
I tested with the query tool and the result should be right, but when I run inside RapidScada, it doesn’t work properly.
There is a limitation with the SQL command inside RapidScada and should I make this sorting on database side?
Thank you!