Query broke after converting to named query

I recently had an SQL Query assigned to an LED display value, in order to run the query in the client view I needed to change it to a Named Query so it would run on the gateway instead of on the client (permissions issue). When I copied the sql query and created a named query the query stopped returning the correct value. The query checks the DB based on operator submited text and returns a 1 or 0. What am I missing when I changed to a named query that would cause the query to return a 0 when it should have returned a 1?

Share the query...

SELECT Decode(count(*),0,0,1)
FROM core_batch_report_load
where batch_no = (select batch_no from core_app_display_data where batch_no = '{Root Container.Batch Number Data.text}' and original_serno = '{Root Container.Serial Number.text}')
and inventory_material = '{Root Container.Generic Data.text}'
and batch_no = '{Root Container.Batch Number Data.text}'

There's a button on the binding to change it into a named query; did you press that, or manually copy and paste?

The parameter substitution syntax is completely different between SQL bindings and named queries, in the sense that named queries...actually have a parameter substitution syntax. So the query should be something like:

SELECT Decode(count(*),0,0,1)
FROM core_batch_report_load
where batch_no = (select batch_no from core_app_display_data where batch_no = ? and original_serno = ?)
and inventory_material = ?
and batch_no = ?

And at the binding, you'll pass in the different parameters you want.

I clicked the convert, but not sure what it is asking for when it says Enter path for select query.

The path to the select query would be where the converted query landed. You should have a little helper tool to help you choose it, as well. About :55 seconds in here: Named Query Binding Video at Inductive University

Great! Thank you. This did not solve my issue. For some reason this query, SQL or Named works in local host but will not work once it is added to the gateway and opened in the client.

Sorry, this is a oracle SQL query that works in the designer but will not work in the client viewer.

You need to enable "Legacy Database Access"

https://docs.inductiveautomation.com/display/DOC81/Vision+Project+Properties#VisionProjectProperties-VisionPermissionsProperties

I was wrong :grimacing: Thanks for correcting me Paul

You don't need that for a properly converted named query.

I would recommend contacting support at this point, Chad. Or share some screenshots of how you've got everything set up and the full details/stacktrace of the error you're seeing.

While trying to convert to a Named query What is the "Path for Select Query"? Not sure what to type there?

It's whatever you want to put. It will be used as the location for the new named query resource that gets generated. You should use whatever folder structure/naming convention you'll find maintainable in the future; slashes are allowed and will be turned into folders.

The error on the server I am getting is "The current user does not have the required roles for this". That is why I am trying to change it to a Named query, so the query runs on the gateway instead of the user running the query from the client.

Also getting the below message for the Named Query.

Update: Thank you all for the help on this. I did find out the issue with the queries after I changed them to Named Queries. I had a permissions setting that was set to authenticated. By changing it back to public my queries began working in the client.

3 Likes