Add ' character to where clause

Ignition
1

For some reason I can’t figure out how to enclose a tag with ‘ ‘ for the where clause in a BLOCK transaction.

I can put Code= ‘0001C’ and that works

but I can’t figure out what combination will do

Where the value of the tag {tag} = ‘0001C’

Code=‘{tag}’ 
Code={tag}

incorrect syntax near ‘C’

I’ve tried:

Code= “‘“ + {tag} + “‘“
Code= ‘‘‘‘ + {tag} + ‘‘‘‘
Code= concat(“‘“, {tag}, “‘“)
Code= concat(str(‘), {tag}, str(‘))
Code= concat(char(39), {tag}, char(39))

edited for code enclosures and prior test scenarios

2

Code = {tag}

The SQL engine will recognise it as a string.

Please see Wiki - how to post code on this forum.

4

That was the first thing a I tried. I see I didn’t include that in the original post.

Code = {tag}

and got the error

Error during group execution. com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'C'. Incorrect syntax near 'C'.

So it appears the tag is not being sent as a string.

If I set the value ot {tag} to the literal string ‘0001C’ then

Code={tag}

works

5

Show us your whole query. Please see Wiki - how to post code on this forum.

6

There is no query, per se. This is in the Where portion of the Table action for a block item transaction. I don’t see a way to view the actual query.

image
image362×261 11.2 KB

7

Sorry, I've never used the Block Group so I don't think I can help further. There is a section in the docs and there's an IU video. Block Group | Ignition User Manual.

8

Understandable. The videos and manual don’t get this deep into the specifics. Typically one of the “cheat codes” above works.

9

This looks like a bug in the parsing logic.

As a workaround, can you try adding that tag as a run always expression and set it as a string?

Then use that in the WHERE clause?

10

Great idea. That didn’t work but I can add an expresssion item tag and use.

concat("'",{[default]tag},"'")

Then the where clause is

CODE={[~]Expression Item}

that’s a valid workaround. Thanks

11

If I was you, I would still open a ticket with support so they can investigate and fix if it turns out to be a real bug.

12

Does it work if you use an expression tag without the concat with the apostrophes in the where clause?

CODE='{[~]Expression Item}'

What version of Ignition are you running? I’ve definitely done this before in standard transaction groups.
I’m curious if the issue is specific to one of the following:

  1. Referencing a tag from outside the group
  2. Block transaction groups specifically
  3. The version of Ignition
13

Funny you should mention that. I did submit a ticket.

14

Interestingly, that does work.

Using version 8.3.0