Ignition Perspective Named Query with WHERE IN Clause

Ignition
1

Hello, I need to create a Named Query using WHERE IN a multiple selection on a form. Can this be done?

Thanks in advance

20Apr20_WhereInClause
20Apr20_WhereInClause1209×560 31.4 KB

2

You’ll have to use a parameter of type “QueryString” and supply the properly formatted segment of the query in a string. Instead of using the colon prefix to the parameter, use curly braces around the parameter name.

3

Thank you for your quick response. I saw an example.of that earlier, but when I tried it I was unable to get it to work. When I made my 2nd parameter a query string I got the yellow triangle. Then when I tried to execute the query I got an error message. Do you have a good example you can send me? I will try again.
Maybe, I just missed something.

Thanks.

4

Have you looked at the manual?

1 Like
5

If you turn the logger for GatewayNamedQueryManager to debug, you can see how the gateway constructed the query it’s trying to run. That may help you figure out what you’re doing wrong.

2 Likes
6

Thank you both. I just got it working. I used QueryString and then used:

WHERE CAST(Date AS DATE) = CAST( :DateSelected AS DATE) AND Department in ({DeptSelected})

I then had to go to the View and convert my dropdown list where I have multiple items selected to a global session value as ‘1’,‘2’,3’ for example.

Thanks!!

David

7

Hi, @David.Bell can you elaborate on how to convert the dropdown list containing multiple values to a global session value containing selection values separated by commas so as to input the same in the query string parameter?

8

This is really a question on a different topic unrelated to this thread. However …
I think you are asking how to bind a multi-select dropdown ‘value’ to a session variable.

  1. In the Project Browser, select Perspective.
  2. In the Perspective Property Editor | Session Custom, add a new custom property of type Array. Call it ddSelection or whatever.
  3. On your view, select the dropdown and in the Perspective Property Editor add a property binding on props.value to session.custom.ddSelection and make it bidirectional.

For debugging purposes add a label to the view, bind it to the session variable, turn on preview and you should see a list of selected values or [] when nothing is selected.

9

hi @Transistor, everything works perfectly. As you mentioned, I am getting , when there is no selection, and [A, B] let's say when A and B are selected. But for my named query to work, I need this to be ['A', 'B']. This I am not able to achieve. Can you kindly help me in that regards too..

Thanks

10

Dropdown to SQL
Dropdown to SQL925×485 73.8 KB

Try quoting your dropdown values.

11

You’ll need to script it as the named query is expecting a string, not an object.

Use a script transform on a property binding to your dd selection:

return "'" + "','".join({value}) + "'"

This will return your selected values as a string with quotes and comma separated

13

@nminchin this works like a charm. I was tryna achieve the same by converting the array into a string and then using regex on the same. Also many thanks to @Transistor for the other answers.

2 Likes