Variable in SQL query

I am trying to configure a dataset to read data from a table.
The scripting should be like the following:
SELECT t_stamp, FIC01_SP, FIC01_PV, FIC01_CV FROM trend_pid

Because it’s a faceplate trend dataset, the FIC01 should be a variable, able to change to FIC02…FIC0n.

I then changed the query to
SELECT t_stamp, {Root Container.TAG_NAME}+’_SP’,{Root Container.TAG_NAME}+’_PV’,{Root Container.TAG_NAME}+’_CV’ FROM trend_pid
But it won’t work.

Can anyone advise what’s the proper way to assign variable in SQL str and how to comibne two string in a SQL query?

Not at PC, but Im pretty sure you just add the indirect part directly.
Eg.
SELECT {Root Container.TAG_NAME}_SP FROM…

1 Like

Thank you for the help.

It worked out very well.

One further question is:
How to add expression in SQL Query?
I want to query the following string, but it won’t work:
SELECT t_stamp, {Root Container.TAG_NAME}_SP, {Root Container.TAG_NAME}_PV
FROM trend_pid
WHERE t_stamp > dateArithmetic(now(), -1, “hr”)

Add a custom property to your root container to compute the date expression. You probably want to use the dateFormat() expression to force the format to something compatible with your DB (default conversion from java.util.Date isn’t acceptable to many DB flavors). Then your query becomes:

SELECT t_stamp, {Root Container.TAG_NAME}_SP, {Root Container.TAG_NAME}_PV
FROM trend_pid
WHERE t_stamp > '{Root Container.OneHourAgo}'

Thank you. It is a good solution.

You could also just use the SQL language to do this as well, for example:

-- SQL server
DATEADD(HOUR, -1, GETDATE()) 
2 Likes