Indirect SQL tags in queries

Quick question about indirect SQL tags- how can I embed one in an SQL query, i.e.

SELECT ProductName
FROM Products
WHERE ProductID =('[]'+{Root Container.System_1.SystemMoniker}+'/ProductID_Setpoint')

I do something like this with expressions when using the tag(…) format, but can’t seem to get it to work in a query.

What I did instead is create a dynamic property where I can concat the entiire ProductID , and then use that in the query, but it’s an extra step that I would like to avoid if possible.

I imagine the system is submitting the SQL query to your database exactly as you write it. If this is the case, try changing you query to SELECT ProductName FROM Products WHERE ProductID=CONCAT('[]', {Root Container.System_1.SystemMoniker}, '/ProductID_Setpoint')

Ok, revisiting this. I’m still having a problem with passing in a dynamic property.

I have a table, and I store the actual query as a string in a table dynamic property. That way, I can create the query dynamically with other code or select a query with radio buttons. I might have multiple queries called Q1, Q2, Q3, etc, and in the table SQL Query binding, I just enter the string property (i.e. {Root Container.Table.Q2}). It works great until I have another dynamic property embedded in the query.

For instance, this is my query:

SELECT p.path_ndx,p.PathID,m.moniker as motor,d.moniker as drive,s.moniker as switch,p.sequence,p.source,p.destination  
FROM paths AS p 
INNER JOIN MotorGroups as m  on m.motorid = p.motorid
INNER JOIN Drives as d  on d.driveid = p.driveid
INNER JOIN Switches as s  on s.switchid = p.switchid
where p.pathid = {Root Container.DefinePaths.ListPaths.Selected} order by p.sequence

If I enter this in the SQL Query binding window, it works fine, but if I paste it to a string and enter that in the SQL binding, I get this error: “Unrecognized SQL escape ‘root’ at line position 317.” Is there a work-around, or can I just not do this?

Well, if you store it in a string before it goes into the query binding, you’ll need to use an expression to insert the dynamic value.

Something like:

"SELECT p.path_ndx,p.PathID,m.moniker as motor,d.moniker as drive,s.moniker as "+ "switch,p.sequence,p.source,p.destination "+ "FROM paths AS p "+ "INNER JOIN MotorGroups as m on m.motorid = p.motorid "+ "INNER JOIN Drives as d on d.driveid = p.driveid "+ "INNER JOIN Switches as s on s.switchid = p.switchid "+ "where p.pathid = "+ {Root Container.DefinePaths.ListPaths.Selected}+ " order by p.sequence"