Parameterized Query Fails Mysteriously

[SteveO]

It all started with the following named query, lets call this Q1:

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:

Q1 Test Results:

Q1 Binding Detail:

Q1 Result on the Page:

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:

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:

Q2 Test Results:

Q2 Binding Detail:

Q2 Binding Error:

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]':

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!

[ryan.white]

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.

[SteveO]

Thank you for catching that oversight... now implemented as '=', unfortunately the problem with Q2 still exists.

[Transistor]

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.

[michael.flagler]

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.