Conditional and parameterized named query syntax

DISCLAIMER: This might be more of a SQL question than an Ignition one.

I have a table on Perspective and I want to bind its data property to a conditional and parameterized query. My view (shown below) has a multistate button and two date entry fields. Depending on the value of the multistate, I want to make the query check different columns for the records in the date range.

I want to use the “MEL_Cal” named query shown below. I’ve setup the parameters and am aware of the “:ParameterName” syntax. What I can’t figure out is how to leverage :FilterMultistate to decide whether to use column “Last_CAL” or “CAL_Due” for the appropriate date range. Can anyone help with the syntax? I assume I need some CASE WHEN THEN in there.

Thanks,
M Murphy

I would have multiple separate queries built that do the where clause and then just set the binding of the table using the selection.
Using a CASE WHEN in a Where clause is tricky without changing to a Stored Procedure that can take the CASE value as a parameter.

You could do something that I’ve done recently, although it might be a bit of a bodge.

I used this as my named Query:

EXECUTE sp_executesql  :query ;

With an entire query as parameter.
I then used expressions and transforms to build a query in a custom prop and used that as the parameter for my named query. this makes it so the query can be changed dynamically with logic.
Each part of the query is another custom prop.

You could also use the QueryString functionality available to NamedQueries.
Create some custom property somewhere on the MultiState Button which determines it’s own value based on which is selected

Expression Binding (if MSB controlValue is 1, then ‘Last_CAL’, else ‘CAL_Due’):

if(self.props.controlValue = 1, 'Last_CAL', 'CAL_Due')

Then, in your named Query set one of the parameters to be a QueryString instead of a value.
22%20AM

And set up your query along the lines of

SELECT MEL_Table.ECN, MEL_Table.Description, MEL_Table.Area, MEL_Table.CAL_Code, MEL_Table.{parameterName} FROM MEL_Table WHERE MEL_Table.CAL_DUE IS NOT NULL

Finally, in your Named Query binding for the Table, make sure you select the custom property discussed above so that your query receives the expected column name.

2 Likes

Hi,
Is it possible to send multiple columns in a query string, the same way you did,(but more…)

Like
select colA,colB, :multipleCols
Here :multipleCols is a querystring containing a variable set of columns.

The reason i ask is because my current query is not working:
myDict={'newPK':newPK, 'oldPK':oldPK, 'colStr':str(colStr)} system.db.runNamedQuery('Sequences/duplicateParFile',myDict)
where
colStr='ColA, ColB, ColC'
(variable amount of columns)

You could use this method as long as your columns are a string and not a dictionary, formatted along the lines of "colName, colName2, etc"

I believe that should work, as the QueryString is I believe interpreted as an exact String substitution.

I ended up using a solution based on this approach. My solution looks at the active tab index from the tab container and at the MSB (I used a Switch expression with IF statements in the cases) to determine the string value on my custom parameter. I then passed that along to the named query as a query string. The binding on the custom param looks like this:

switch ({…/TabContainer.props.currentTabIndex},
0,1,2,
if({this.props.controlValue} = 1, ‘Last_CAL’, ‘CAL_Due’),
if({this.props.controlValue} = 1, ‘Last_PM’, ‘PM_Due’),
if({this.props.controlValue} = 1, ‘Last_RQ’, ‘RQ_Due’),
‘UNKNOWN STATE’)

The only thing that took me a while to figure out was that the syntax to incorporate query strings is different than that to incorporate a value parameter. {parameterName} vs :parameterName

Thanks for your input and also thanks to @MMaynard and @RadicalRumin !!

1 Like

This is actually what I have been trying to do, but the code will not work. It looks like the compiler cant handle a query string in this type of query…

Look at the attached picture:


In the attached namedQuery. i have tried replacing ‘:parStr’ with :parStr. Which also does not work

If i call the named query with the following, it will not work:

newPK=1
selSeqPK=0
colStr= "ColA, ColB, ColC"
myDict={'newPK':newPK,'selSeqPK':selSeqPK,'parStr':colStr}
system.db.runNamedQuery('Sequences/duplicateParFile',myDict)

The query itself is working fine. (i have deleted the queryString and substituted the string directly)

Try using curly braces around parStr instead of the colon marker.

1 Like

Hey @tordvd. I’m attaching a screenshot of my named query. I hope it helps with figuring out the syntax you need.

1 Like

Wow, thanks to both of you. @pturmel and @mmurphy