Use an expression on a parameter in a Named Query

Ignition
1

Hello everyone,

I am currently programming a Numeric Text Field binded with a Named Query.

The query is the following :

SELECT AVG(floatvalue) FROM dbo.sqlt_data_1_{Year}_{Month} WHERE tagid = 1

In my window i have two Dropdown Lists, from which you can choose the Month and the Year. The QueryString parameters are found through the expression {Root Container.Dropdown 1.selectedStringValue} (for the Year, and changing the name of the dropdown for the Month). Until this point everything works perfectly.

Now, what i want to know is whether it is possible to choose a Month and to access the data of the previous month. For instance, let’s say i pick Month 4 in the dropdown list but in my named query i actually want to access the data for Month 4 but also for Month 3 (which is in sqlt.data_1_YYYY_03).

I can’t figure how to do this… I thought about writing a script which would process that (and tackle exceptions like Month 12 of Year 4 being the month before Month 1 of Year 5) but maybe someone here has already done something similar ?

Thank you in advance,

2

You would have to construct the other strings externally and pass them as additional parameters. The SQL would need to nest a UNION query to combine the two source tables before the outer query applies your aggregate function. Avoiding this sort of complexity is why the Tag History Bindings exist.

2 Likes