# Named Query Returning Empty List **URL:** \ **Category:** General Discussion\ **Tags:** perspective\ **Created:** [September 21, 2023, 12:32am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430 "2023-09-21T00:32:24Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Tucker\_Gustavesen](https://avatars.discourse-cdn.com/v4/letter/t/43a26b/32.png) [@Tucker\_Gustavesen](https://forum.inductiveautomation.com/u/Tucker_Gustavesen)\ **Post date:** [September 21, 2023, 12:32am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/1 "2023-09-21T00:32:24Z")
Hello, Looking for some help with a script I have that's triggering a named query. The named query is ```auto Select linkID from Recipe_Links where recipeID = :recipe_id AND Status = 1 ``` and the section of script that's running it is: ```auto named_query_path = "get_link_ids_by_recipe_id" named_query_params = {"recipe_id": max(recipe_ids_list)} IDs = system.db.runNamedQuery(named_query_path, named_query_params) IDs_list = [x[0] for x in system.dataset.toPyDataSet(IDs)] message = "%s" %IDs_list system.perspective.openPopup("", "Popup/MessageBox", {"Message" : message}) ``` I have verified that the "max(recipe\_ids\_list)" is returning the integer I expect, so there are definitely valid records that should be getting selected by the query. I can run the query in the testing section of the named query editor, feeding it a known-correct integer value, and it does return the records I expect. I've quadruple checked that the name I'm using in the script is correct and that the parameter name is correct too. Does anyone have an idea what might be going wrong? I get no error popups, no errors in the gateway log, and the rest of my script executes fine (minus the part it's supposed to do if there were any values in the IDs\_List) ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [September 21, 2023, 2:34am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/2 "2023-09-21T02:34:14Z")
Hmmm.. That should work. The only things I can thing of: - `system.perspective.print` the `max(recipe_ids_list)` in your script execution to make sure its the right ID (I know you mentioned that you already verified, but I want to stress that its important to verify it as your script sees that variable). - 8.1.29 now returns Named Query results as PyDataSet by default, so if you're on a more recent version, maybe that's what's causing the problem (you're casting to PyDataSet when it's already a pyds) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/1/31da929b39238b8a29cc29fe31a8439b5146cd5b.png) - Make sure that as your script executes, you're actually running that part of your script. It's hard to tell without seeing the entire script, but I usually put a print statement before and after a section to make sure it actually gets executed. The gist of my answer is: I'm grasping at straws because what you have in the question looks correct to me. I just verified with a sample query and it should work. I was hoping it was something obvious, like you missing a database parameter to your named query (that should trigger an error) or you calling the `parameter` parameter to the `system.db.runNamedQuery` `params` instead of `parameters` (idk why it's called `parameters` here but `params` everywhere else - this still gets me once in a while). ---
**Author:** ![Tucker\_Gustavesen](https://avatars.discourse-cdn.com/v4/letter/t/43a26b/32.png) [@Tucker\_Gustavesen](https://forum.inductiveautomation.com/u/Tucker_Gustavesen)\ **Post date:** [September 21, 2023, 3:05am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/3 "2023-09-21T03:05:04Z")
'preciate the help here! I can confirm the script executes up to that point, I have a message box popup getting fired and printing the value of `max(recipe_ids_list)` executing before the sample I gave there, and I also confirm that the messagebox after the query runs is executing and printing "" showing me my empty list. It does feel like it's some issue with the input data type though, as I can run the following section of code with a static integer value passed in for "recipe\_id": ```auto def help(recipe_id): named_query_path = "get_link_ids_by_recipe_id" named_query_params = {"recipe_id": recipe_id} part_link_ids = system.db.runNamedQuery(named_query_path, named_query_params) part_link_ids_list = [x[0] for x in system.dataset.toPyDataSet(part_link_ids)] return part_link_ids_list ``` That said, the earlier sample still gave me an empty list when I ran it with the same static integer under the same conditions: named\_query\_path = ```auto "get_link_ids_by_recipe_id" named_query_params = {"recipe_id": 1} IDs = system.db.runNamedQuery(named_query_path, named_query_params) IDs_list = [x[0] for x in system.dataset.toPyDataSet(IDs)] message = "%s" %IDs_list system.perspective.openPopup("", "Popup/MessageBox", {"Message" : message}) ``` so I'm still really out of my mind on what's happening. I am on version 8.1.30, but the examples I have of the query working still use the toPyDataSet ---
**Author:** ![Tucker\_Gustavesen](https://avatars.discourse-cdn.com/v4/letter/t/43a26b/32.png) [@Tucker\_Gustavesen](https://forum.inductiveautomation.com/u/Tucker_Gustavesen)\ **Post date:** [September 21, 2023, 3:44am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/4 "2023-09-21T03:44:34Z")
_I was able to resolve it using system.db.runQuery() and giving a query string with a %s to pass in my max(recipe\_ids\_list) parameter. No idea why the unnamed query makes the difference over doing a named query, but my list is no longer empty, so I'm calling that a W_ **REDACTED** as it turns out, my unnamed query approach left out my "AND status = 1" condition from the query. Adding that back into the query, named or unnamed, causes the script to return an empty list, even though it still works properly in the query browser and in the named query builder ---
**Author:** ![amy.sudibyo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amy.sudibyo/32/38659_2.png) [@amy.sudibyo](https://forum.inductiveautomation.com/u/amy.sudibyo)\ **Post date:** [September 21, 2023, 3:51am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/5 "2023-09-21T03:51:56Z")
Is your recipe\_id param in your named query an integer or a string? If it's expecting a string, did you try passing in "1" rather than 1 into your script? ---
**Author:** ![Tucker\_Gustavesen](https://avatars.discourse-cdn.com/v4/letter/t/43a26b/32.png) [@Tucker\_Gustavesen](https://forum.inductiveautomation.com/u/Tucker_Gustavesen)\ **Post date:** [September 21, 2023, 4:03am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/6 "2023-09-21T04:03:38Z")
Unfortunately no, it's looking for an integer and getting an integer ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [September 21, 2023, 4:13am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/7 "2023-09-21T04:13:05Z")
Can you get a screenshot of the query browser where it's working, and the named query definition that isn't? ---
**Author:** ![amy.sudibyo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amy.sudibyo/32/38659_2.png) [@amy.sudibyo](https://forum.inductiveautomation.com/u/amy.sudibyo)\ **Post date:** [September 21, 2023, 4:14am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/8 "2023-09-21T04:14:52Z")
Hmm. Still wondering if it's something to do with parameter passing and data types, that's normally what I find if I have inconsistencies between results in the named query workspace and calling the named query via script. Maybe play around with running the named query from the script console? (Don't forget it's gateway scoped) ---
**Author:** ![Tucker\_Gustavesen](https://avatars.discourse-cdn.com/v4/letter/t/43a26b/32.png) [@Tucker\_Gustavesen](https://forum.inductiveautomation.com/u/Tucker_Gustavesen)\ **Post date:** [September 21, 2023, 4:32am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/9 "2023-09-21T04:32:53Z")
This is the full set of data in the table ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/7/17489e84258264e20c6379a9a0a2d5b5c1e50d3b.png) This is the query I _want_ to run, selecting just the IDs from that table for records that match the recipeID and have status = 1 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/4/74180cdce1c85d9ffa76301e71c3d8f437826b74.png) here is the named query definition that does not work when called using my earlier syntax ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/2/629e404ca7e94ee9ea2be006e49577318dd538b1.png) and here is that same named query working correctly from the testing tab ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/7/9766dc9385f0c4e0cca2400c3e0508dd53ec0e22.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/7/9766dc9385f0c4e0cca2400c3e0508dd53ec0e22.png) seems to only fail on me when I try to use the AND syntax in a query that gets executed in from a script ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [September 21, 2023, 4:43am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/10 "2023-09-21T04:43:32Z")
When you `SELECT *` the column is called `status` and in your query, you're searching for `Status` - column names aren't case-sensitive AFAIK so that shouldn't matter but give that a try? ---
**Author:** ![Tucker\_Gustavesen](https://avatars.discourse-cdn.com/v4/letter/t/43a26b/32.png) [@Tucker\_Gustavesen](https://forum.inductiveautomation.com/u/Tucker_Gustavesen)\ **Post date:** [September 21, 2023, 4:50am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/11 "2023-09-21T04:50:07Z")
had an almost-eureka moment an hour or so ago where I noticed that too but to no avail. The table column is "status" but keeping it lowercase in the query, named or not, did not fix it ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [September 21, 2023, 4:53am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/12 "2023-09-21T04:53:45Z")
That's so strange... what DB vendor are you using? Is your default DB set (although it should be as you're able to run the query in the Test workspace)? Is the data type of `recipe_id` really `Int8` (that translates to `bigint` for MSSQL). ---
**Author:** ![Tucker\_Gustavesen](https://avatars.discourse-cdn.com/v4/letter/t/43a26b/32.png) [@Tucker\_Gustavesen](https://forum.inductiveautomation.com/u/Tucker_Gustavesen)\ **Post date:** [September 21, 2023, 5:05am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/13 "2023-09-21T05:05:50Z")
using MSSQL 2019, default is set, and no it looks like I'm just using regular int in the table at the moment. would that cause issues even though I'm way below the limits of int right now? ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [September 21, 2023, 5:07am UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/14 "2023-09-21T05:07:20Z")
I think if your DB column is of type `INT` and your parameter is of type `bigint`, that may cause issues. I vaguely remember running into something like this in the past, but I don't remember exactly. What happens when you try to set the Named Query parameter type to `Int4`? ---
**Author:** ![Tucker\_Gustavesen](https://avatars.discourse-cdn.com/v4/letter/t/43a26b/32.png) [@Tucker\_Gustavesen](https://forum.inductiveautomation.com/u/Tucker_Gustavesen)\ **Post date:** [September 21, 2023, 12:26pm UTC](https://forum.inductiveautomation.com/t/named-query-returning-empty-list/79430/15 "2023-09-21T12:26:27Z")
With the parameter set to Int4 I still get the same behavior; still works if I don't include the "`AND status = 1`" condition in the query