String concatenation issue using tag value in an SQL query

Ignition
1

I’m trying to create a database tag that using a string substitution from a tag value.

Here’s what I’m trying, but it doesn’t work. The only difference between these 2 statements is the tag value vs a string literal. I'm hoping I'm missing something obvious/easy.

This is the query from the dataset tag I'm trying to populate.

SELECT
JSON_VALUE(a.value, '$.areaName') AS AreaName,
JSON_VALUE(a.value, '$.equipPath') AS AreaPath,
JSON_VALUE(l.value, '$.lineName') AS LineName,
JSON_VALUE(l.value, '$.equipPath') AS LinePath
FROM OPENJSON(
(SELECT parameter FROM [dbo].[ProductionModel] WHERE name = concat('Marion','ProductionModel')),
'$.areas'
) AS a
CROSS APPLY OPENJSON(a.value, '$.lines') AS l
WHERE JSON_VALUE(a.value, '$.areaDisplayOrder') >= 0;

This doesn’t.

SELECT
JSON_VALUE(a.value, '$.areaName') AS AreaName,
JSON_VALUE(a.value, '$.equipPath') AS AreaPath,
JSON_VALUE(l.value, '$.lineName') AS LineName,
JSON_VALUE(l.value, '$.equipPath') AS LinePath
FROM OPENJSON(
(SELECT parameter FROM [dbo].[ProductionModel] WHERE name = concat({[.]Location},'ProductionModel')),
'$.areas'
) AS a
CROSS APPLY OPENJSON(a.value, '$.lines') AS l
WHERE JSON_VALUE(a.value, '$.areaDisplayOrder') >= 0;

The error from the second one results in Invalid column name ‘Marion’, so it’s reading the tag value but not concatenating it. The first one works fine, so concat works with two strings, but not if one value is from a tag.

What am I missing?

Thanks!!!

2

With a SQL tag, substitutions are done before the SQL is built.

That is, a tag substitution is not a safe parameterized substitution - it is a direct string replacement.
(SELECT parameter FROM [dbo].[ProductionModel] WHERE name = concat({[.]Location},'ProductionModel')),
Is becoming
(SELECT parameter FROM [dbo].[ProductionModel] WHERE name = concat(Marion,'ProductionModel')),

You need to add the quotes around the tag value yourself:
(SELECT parameter FROM [dbo].[ProductionModel] WHERE name = concat('{[.]Location}','ProductionModel')),
So that the end result i what you want:
(SELECT parameter FROM [dbo].[ProductionModel] WHERE name = concat('Marion','ProductionModel')),

3

That did the trick.

Ugh. I know I tried that at one point, but I must have had a typo or something. At least it's working now.

Thanks!!!!