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.
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’):
Then, in your named Query set one of the parameters to be a QueryString instead of a value.
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.
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)
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:
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
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…
