The results of a SELECT statement can be filtered down to a smaller subset by adding a WHERE clause, allowing the results to only contain records that meet some criteria. Learn how to add a WHERE clause to a Named Query in this lesson.

Video recorded using: Ignition 8.0


(open in window)

[00:00] In this lesson, we're going to take a look at how we can filter our select query, so that only certain rows are returned from the table. As you can see, I have copied the query from my previous lesson into a new named query called filtered select. With my filtered select query, I still want to select all of the data from the inventory table, but this time I'm going to add a little bit to it like so. To filter the data that's returned from a select statement we need to use the where clause. The where clause allows us to list a condition that needs to be true for that row in order for it to be returned. In this case, I am checking to see if the rows name column is equal to the string work stations.

[01:05] Any rows that match that condition will be returned, and any rows that do not will not be returned. We can go to our testing tab and click on the execute query button to test this out. You can see here that each of the rows returned has a name of work stations. A few things to note about the where clause. The condition can use any of the columns in the table. So rather than name, I could have made my condition about the quantity, instead. Additionally, the column that is being used in my where clause does not actually have to be selected with my select query. So for example, I can select name and location, but my where clause will utilize the quantity column. There are also a handful of operators that I could use to create my condition, rather than just equals, I can use things like not equals, greater than, less than, even like to match to a subset.

[02:11] I can also specify multiple conditions that need to be met for the row to be returned. I simply need to separate these conditions with either the or or the and keywords. Two conditions separated by and requires that both of them be true, whereas two conditions separated by or require that only one of them be true. Finally, I can have the value part of my condition be dynamic. Rather than hard coding in a name like workstations, I can add a parameter. In this case, I want the data type to be a string and the name to be name. I'll drag my parameter down into my where clause, and now the value of the parameter is going to help determine the condition that's needed for the row to be returned. I can go to my testing tab to try this out. I'll look for a value of printers and execute the query and you can see now only the rows where the name is printers gets returned.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.


Share this video