How can we set a variable in select sql using "system.db.runQuery"?

Ignition
1

The original table I wanna query contains no index, while I wanna add index to make the final display more user-friendly.

I first tried it in mysql and it worked like this which is great and convenient.

image
image966×700 23.2 KB

However, when I put the same sql query into “system.db.runQuery”, there exists an error in the sql query:

image
image1917×1035 92.7 KB

I also tried named query using the same way to set a variable but it failed too.

I know there are other ways that allow me to add index to the display table, but for me , to add it in the query is the most ideal method and I really wanna make it work.

Looking forward to your help, guys!!!

2

You’re running 2 queries in a single call. You can’t do that with system.db.runQuery()

Query 1
SET @i := 0;
Query 2
SELECT ......

Not sure if you can just call the runQuery twice, one for each statement, or if you need to begin a transaction for the session variable to work.

EDIT
Okay, so I got curious and tested it out.
This is possible to do in a single statement.

SELECT
	(@i:=@i+1) as 'index',
	t.time as 'time',
	t.v as 'v'
FROM send_h t, (SELECT @i:=0) itterator
WHERE t.time BETWEEN '2021-04-02 12:00:00' AND '2021-04-03- 12:00:00'
4

OMG！！！！
Thank you so much my dear friend!!! @Claus_Nielsen