I have a simple named query that works via the Testing tab on the query builder. I can test it with: TownA as Plant and I get results I expect:
SELECT [MachineID]
FROM machines
WHERE [Target] - [Bag] = 0
AND [State] NOT IN(4, 65535, 56, 1)
AND (
(:Plant = 'TownA' AND [RoomCode] LIKE 'WB%')
OR
(:Plant = 'TownB' AND [RoomCode] NOT LIKE 'WB%')
)
ORDER BY MachineID ASC
When I attempt to bind the query to a label, I get the error:
Error_Configuration("RuntimeException: Syntax Error on Token 'End of Expression' (Line 0, Char0)")
This happens when I put the Plant value in without quotes( TownA ), as I've tested with in the query builder.
If I I surround the Plant value with quotes in the binding ( "TownA" ), it accepts it. However, I get an empty dataset as the result, since with quotes my query doesn't match, and doesn't return results. (If I test with quotes in the query builder, I also get no results).
How can I get this to work? It seems like it should be simple, but I'm not getting anywhere with it.