Named query works in testing, but not with a binding

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.

This seems backwards to me. Why would it not be something like

[Plant] = :Plant

You would provide TownA, TownB, etc. as the Plant parameter.

2 Likes

The query isn't really the problem. It tests fine, but I'm not able to use the query in the binding. I have tried doing the query other ways (which also give me the expected results in the testing tab of the query builder), but I run into the same issue when attempting to use a binding.

You are going to have to show how you are providing these parameters to your named query, the bindings.

It's working now...It seems I just needed to hit save after creating the query before it was really functional in Designer.