[00:00] In this video, we are going to talk about using a SQL query expression item. in SQL query mode they can typically be used in one of two ways. They can execute a SELECT statement to retrieve a value that can then be used by the group or written to the database, or they can be data modification statements such as update, insert or delete where they might reference items in the group in order to write those values to other places in the database. A SQL query expression item can do all of the things that other expression items can do. For example, it can write to database columns or it can write to other items and it can reference the group's items inside of their query. Let's take a look at selecting a value out to use as a trigger. I'm going to make a new expression item and change the type to SQL query in the item configuration window, besides the text field for writing the query, we have the ability to set the data source that we will target which can be the same or different than that of the group as well as the setting for the query type.

[01:19] The item has to know whether the query is a select or an update query, and usually can auto detect, but for more complex multi line queries or when Column stored procedures it may be necessary to set it directly. Our example group here updates the first row of a table and has a column called trigger column. So as an example, we'll write a query that will select that value from the table, and we'll use that to trigger the group. I've written this simple SELECT statement and I'll rename the item and set it to run always because I want it to always be checking the value. Now I'll set the group trigger.

[02:05] Set it to true and enable and save the group. If we go to the database and update the column on the next execution the SELECT statement will see the new value which matches the trigger condition and the group will begin to trigger which we will see as the ramp value being updated. Now let's look at inserting a value into another database table each time this group execute it. It would probably be easier to just create a second transaction group for what we are about to do but hopefully this serves to demonstrate what can be done. We will create a new expression item and set the name. We'll leave it set to run on the trigger and we'll come over and create a SQL query, set it an insert statement. At this point, we'll reference a tag in our group that has the value that we want.

[03:08] The reference will get injected directly into the query as if the value had been typed by hand. If we save the item, that's all we need. Now, each time the group executes and is triggered that insert statement will be run with the current value of our ramp tag. We can see in our database, our original table where the ramp value is still being updated. And in the second table we can see the new ramp values being inserted. SQL query expression items provide a great way to interact with data from different parts of the database. The ability to query from different database connections than the group is set to use is a great way to get data from other systems. However, it's important to keep in mind the impact that running queries can have on a system, creating many SQL items inside a large number of groups can really drive up the average query count against your database so it's important to be mindful of the number of queries that you create, and when they get executed.

[04:12] Running queries on triggers can be a way to improve the situation and avoid problems.