SQL Query with realtime tag values

With the query below, I’d like to use it to build a table that shows be both an offline (DB) and online (PLC) value for some recipe management. I’d like to do this with a query alone but am having issues getting a dynamic tag to resolve. Any help would be greatly appreciated, I’m hoping it’s just semantics.

SELECT
s.id,
p.name,
s.value as offline,
‘{Control/Axis/’ || ‘{Root Container.data::Meta.TagName}’ || ‘/stParameters/’ || p.tag || ‘}’ as online,
u.unit,
s.usersave,
s.datesave
FROM
data_element_settings as s LEFT JOIN
data_element as e on s.elementid = e.id LEFT JOIN
data_element_params as p on s.paramid = p.id LEFT JOIN
data_element_params_units as u on p.unitsid = u.id
WHERE
e.tag = ‘{Root Container.data::Meta.TagName}’

Your database doesn’t have access to your tags. You can only get back from your DB what it holds and what you pass to it. What you can do is reprocess the dataset after it arrives from the DB to pick up values from Ignition. This requires scripting and/or functions like view() from Simulation Aids.

Hey Phil, thanks, I understand that.

Ignition resolves a tag value in the query if its bound by ‘{…}’, I just haven’t had success dynamically building the tag path with a value from the database, was hoping to avoid writing on entry or timed code.

Thanks!

The DB can’t do that. The braces are completely resolved in Ignition before the SQL is sent to the DB – there’s no mechanism or opportunity to do what you are attempting. Also, you can’t use any operators inside the braces. Ignition only allows a single tag path or component path or property name inside braces, and no nesting.

2 Likes