Hi,
In 7.9.16 I was able to do:
SELECT {index} FROM {table}
WHERE t_stamp::date = {[.]date}::date
AND nom_produit = {[.]nom_produit}
ORDER BY {index} DESC
LIMIT 1
Where {index}, {table} are string type parameters and {[.]date}, {[.]nom_produit} are internal UDT tags.
It’s returning: ‘Error expression eval’ on Ignition 8.1
How can achieve that in the new version?
Seems to work for me:
SELECT TOP {TopNumber} * FROM AUDIT_EVENTS WHERE ACTOR LIKE '{[.]ACTOR}'
I've never seen that syntax before with the :: 
I'm suspecting this is where the issue lies (below). Try using '{[.]date}'::date instead
This works:
SELECT TOP {TopNumber} * FROM AUDIT_EVENTS WHERE ACTOR LIKE '{[.]ACTOR}' AND EVENT_TIMESTAMP > '{[.]TIMESTAMP}'
I was curious and went hunting for '::date' . The first obvious place that I found an explicit reference to it was in what is the application of “::date” in redshift?, which prompted me to look for CAST and CONVERT functions which had on it:
CAST
You can use two equivalent syntax forms to cast expressions from one data type to another:
CAST ( expression AS type ) expression :: type
So '::' is effectively a synonym for 'cast'
I've only ever seen it in PostgreSQL. Handy for prepared statements when the data type of a substitution might be ambiguous.
{ I think Amazon Redshift is built on PostgreSQL... }
You’re right about PostgreSQL: Amazon Redshift and PostgreSQL
I’m surprised my google didn’t turn up PostgreSQL first.
Thank you all guys, this works:
SELECT {index} FROM {table}
WHERE CAST(t_stamp AS DATE) = CAST(’{[.]date}’ AS DATE)
ORDER BY {index} DESC
LIMIT 1
Hello all,
I am following the example to understand how to do something similar.
In my case i have a SQL list of stations, and UDT list, were the nimber of the station correspond to the ID of the station.
(SQL)1 , North station → Station 1 (UDT)
Amd i am traying to get the value [default]Stations/Station 1/State tag (int)
Right now I don’t Know how what is the name of stations database, i think is the SQL data
Firstly, you can’t use dynamic parameters in the query browser, so you’ll need to replace all of those {} references with static values.
All of the historised tags are stored in the sqlth_te table which you’ll need to use to lookup the id of the tag you’re wanting to pull the data for and use that id in the WHERE clause of the SELECT.
e.g.
SELECT * FROM sqlt_data_1_2021_01 WHERE id = 12
you could also do the lookup directly in the SQL:
SELECT * FROM sqlt_data_1_2021_01 WHERE id =
(SELECT TOP 1 id FROM sqlth_te WHERE tagPath = 'path/to/tag')
Not sure if “tagPath” is correct, need to check the name
If you’re still having an issue as in your deleted post, the [ and ] are special characters in sql and you either need to 1. remove them, and I can’t remember if the tag provider is stored with the tagpath or not, or 2. escape them using [[]
Now i understand much more.
Thanks
How can i select variable table time data?
tasks.task_description,
tasks.task_name,
sqlt_data_1_2021_02.floatvalue, -> CONCAT(“sqlt_data”,“now(10000)”,".floatvalue")?
sqlth_te.id
FROM sqlth_te
INNER JOIN sqlt_data_1_2021_02 ON sqlth_te.id = sqlt_data_1_2021_02.tagid,stations
INNER JOIN tasks ON stations.station_number = tasks.station_number
SELECT vvalue
FROM HISTORY
WHERE TagName = {TAGNAME}
AND wwVersion = ‘Latest’
AND DateTime = {DATETIME}
Can we add the parameter in the SQL query on the UDT? This is not working on my version…If I need to create an external expression can you provide a link?