Simplify Queries with SQL<br>Expressions

Structured Query Language (SQL) is an industry standard language used to query and manipulate data in relational databases. Behind the scenes, LIMS uses SQL extensively. When you query data using the queryby-example (QBE) controls, LIMS constructs an SQL SELECT statement to locate the data in the
LimsData back end database.

Even if you are unfamiliar with SQL you can still use LIMS’ SQL expressions to simplify your queries. If you frequently enter the same criteria in the QBE controls, you can save time and simplify future queries by saving your criteria as an SQL expression. You can repeat your query by selecting the saved SQL
expression.

An example is a good way to demonstrate the power of customized SQL expressions. The Sample Summary Setup dialog below shows how users at a county with multiple wastewater treatment plants frequently query data for their southeast plant by selecting all locations for the plant. For simplicity, they have used a location naming convention which results in contiguous selections in the sorted list.

Repeatedly selecting the southeast plant locations is somewhat cumbersome and it is very easy to inadvertently omit one or more locations. You can use an SQL expression to avoid these problems and simplify the query.

To create the SQL expression, first use the QBE controls to construct the query. For now, omit any criteria that will vary with each query such as date ranges. Click the [Query] button and LIMS will build the SQL SELECT statement from your criteria and execute your query. Click the SQL tab of the query controls and the SQL expression for your query will be listed in the “SQL for last query” field. Now select the entire SQL expression or the portion of the expression you wish to save. In our example shown below, we have selected the part of the expression representing our location criterion. Use Ctrl+C to copy the selected text to the Windows clipboard then click the [Cancel] button to close the dialog.

To save your SQL expression, open the SQL Expressions screen on the LIMS Setup menu. In the blank new record enter an appropriate name for your expression then use Ctrl+V in the SQL Expression field to paste your expression.

Now that we have saved an SQL expression for the southeast plant locations we can use the expression whenever we need that criterion in a query. To use a saved SQL expression, simply select the expression on the SQL tab of the query controls. In addition to your SQL expression, you can enter any other query criteria using the QBE controls. The screen below shows the results of a query using our southeast plant locations SQL expression along with a collected date range that was entered using the QBE controls on the Sample Criteria tab.

In our example above, the SQL expression “(LocationID IN (L1, L2, … Ln))” requires that the sample’s location, represented by the internal LocationID value, must be one of the locations in the list of values. By using the internal ID values instead of the location names, LIMS is able to construct the fastest query possible. This is a good solution in this case since the list of southeast plant locations will rarely change. However, it is important to note that our expression must be updated whenever the southeast plant location list changes.

Alternatively, we could use a slower but more flexible SQL expression to find samples with a location name beginning with “SE”:

(LocationID IN (SELECT LocationID FROM Location WHERE Name LIKE
‘SE*’)).

Give SQL expressions a try and we are sure you will find useful ways to put them to work in your frequently used or complex queries.