# Parameterized Query Fails Mysteriously **URL:** \ **Category:** Ignition\ **Tags:** python, database, perspective, scripting\ **Created:** [October 4, 2024, 11:43pm UTC](https://forum.inductiveautomation.com/t/parameterized-query-fails-mysteriously/96861 "2024-10-04T23:43:00Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![SteveO](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steveo/32/55271_2.png) [@SteveO](https://forum.inductiveautomation.com/u/SteveO)\ **Post date:** [October 4, 2024, 11:43pm UTC](https://forum.inductiveautomation.com/t/parameterized-query-fails-mysteriously/96861/1 "2024-10-04T23:43:00Z")
It all started with the following named query, lets call this Q1: ```python SELECT [plan], actual, [month], rank FROM control_tower_dataset WHERE metric = 'Pulse Score' AND site = 'MISSION LEVEL' AND segment = 'MIS' AND [year] = 2024 ``` Q1 In the Named Query Design space: ![Snag_10243a57](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/d/cdc5d32517e363e15ac28b22fc5f61a315429091.png) Q1 Test Results: ![Snag_1025a198](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/6/e6d110840f5437dd8b624db481820811d898af61.png) Q1 Binding Detail: ![Snag_1021b4d5](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/4/b4afedb48a42b49ad05f5e178ac92b7f244e01ef.png) Q1 Result on the Page: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/2/22abfe87af7ba5813e88da62cb98dfbb6d4c5d88.png) All is well at this stage... BUT... I need to parameterize the query so I can employ the Dropdown for 'Select Portfolio'. Below is the parameterized version of the Q1, lets call this Q2: ```python SELECT [plan], actual, [month], rank FROM control_tower_dataset WHERE metric = :myMetric AND segment = :mySegment AND site = :mySite AND [year] = (Year(CURRENT_TIMESTAMP)) ``` Q2 In the Named Query Design space: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/5/957ecc6e06acbb5f05117403cb7db7bf1edacd5e.png) Q2 Test Results: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/d/8d53a20a2eda2763645d3714c736880d960ae149.png) Q2 Binding Detail: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/8/78ea470c9ffc58b035647c775fc5600df3c49ae2.png) Q2 Binding Error: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/a/ca476bc609e6728542512643f87e63605dd9873d.png) So what is going on here!?.. I'm totally perplexed here... I've tried everything... the act of parameterizing the Q1 as Q2 runs in the design space but fails when I try to bind it?! Additional information: The binding error suggests that the SQL parser doesn't like the '[' from this line... SELECT [plan] If I Open the Query Builder in the Q2 design space I notice that 'plan' is no longer a bracketed '[plan]': ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/1/51511801930086c4ac0435299a59026aac0ef360.png) All of the other column names that would/could be reserved names retain the brackets ''. Additionally, the formatting of the query is not the tidy code that I wrote (which really bugs me). These are the things I've tried for Q2: 1. Remove the brackets around [plan]... it fails (I definitely know that plan is a reserved name) 2. Fully qualify all of the references to columns by a preceding with the table name: control\_tower\_dataset.[plan]... it fails I have no idea what to try next and I really don't understand why Q2 runs in the query designer and produces acceptable results but fails in a binding, Any help or ideas as to what I may be doing wrong is greatly appreciated. Thank You! ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [October 4, 2024, 11:47pm UTC](https://forum.inductiveautomation.com/t/parameterized-query-fails-mysteriously/96861/2 "2024-10-04T23:47:49Z")
You have an incorrect expression for `mySite` in the binding configuration. You are using `==` which is python syntax for equality, expression language uses a single `=` for equality. ---
**Author:** ![SteveO](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steveo/32/55271_2.png) [@SteveO](https://forum.inductiveautomation.com/u/SteveO)\ **Post date:** [October 5, 2024, 9:37am UTC](https://forum.inductiveautomation.com/t/parameterized-query-fails-mysteriously/96861/3 "2024-10-05T09:37:25Z")
Thank you for catching that oversight... now implemented as '=', unfortunately the problem with Q2 still exists. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 5, 2024, 10:21am UTC](https://forum.inductiveautomation.com/t/parameterized-query-fails-mysteriously/96861/4 "2024-10-05T10:21:26Z")
I can't see the problem but what I would try next (in the absence of any better ideas) is to check the parameters. - Copy the named query and edit it down to, `SELECT :myMetric ` - Copy the binding to a table, select the new query from the dropdown and remove the transform. - If that shows the :myMetric parameter correctly then modify the named query for :mySegment and finally for :mySite. It might show up a problem with an invalid parameter. * * * Afterthought: you **must save the project** before updates to a named query take effect. ---
**Author:** ![michael.flagler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.flagler/32/26272_2.png) [@michael.flagler](https://forum.inductiveautomation.com/u/michael.flagler)\ **Post date:** [October 5, 2024, 8:08pm UTC](https://forum.inductiveautomation.com/t/parameterized-query-fails-mysteriously/96861/5 "2024-10-05T20:08:16Z")
Should Pulse Score in your parameters be inside quotes? I think it's expecting an expression and if using a static value needs to be in quotes.