Basic SQL Query

Basic SQL Query

Watch the video

This query type is the common type typically seen through much of Ignition before version 7.8. You can write queries which include Tag path references, expressions, or report parameters which resolve at run time.

You can use the following code in a static SQL query. The query will return all rows where the value in the MachineType column is 'Washer'.

SELECT MachineName, BuildingNum, MachineID
FROM MachineStorage
WHERE MachineType = 'Washer'

Report Parameters in a Basic SQL Query

Queries can also be made dynamic using things like report parameters. To insert a report parameter, click the Parameters icon to the right of the query area and select your parameter. This allows for a more dynamic query, since new values can be passed into the parameter at runtime, giving the ability to change the type of machine this query is looking for.

Parameters are inserted directly into a report. This means that the datatype of parameter will affect how it should be referenced in the query. For example, since the parameter MachineType is a string, it will need a single or double quotes around it.

String Parameters

WHERE MachineType = '{MachineType}'

Since an integer does not need quotes around it, if your parameter is a Long, Double, or a Boolean, you can directly place the parameter in your query, without the quotes.

SQL - Long, Double, and Bool Parameters

WHERE MachineNum = {MachineNumber}

Working with Dates

If your parameter is a date object, then special consideration must be made.

The query will not accept a date object directly, it must first be converted to a string by putting quotes around it. However, database generally prefer datetime objects to be converted to strings in very specific formats, such as yyyy-MM-dd HH:mm:ss. This means we need to reformat the date on any report parameters we want to pass to the Basic SQL Query. There are two main approaches to this:

Reformat the Date Parameters

By utilizing the expression language's dateFormat() function, we can simply specify the format of the date.

SQL - Reformatted Date Parameter

dateFormat(
    dateArithmetic(now(), -8, "hr"), //don't forget the comma at the end of this line...
    "yyyy-MM-dd HH:mm:ss")          //...as well as the outer closing ')'

Create New Formatted Parameters

In some cases, you may wish to leave the original "raw" Date parameter alone, and create a display-friendly version as a string.

To do this, simply make a parameter with type string and use the dateFormat() expression on a date. In the image below, you can see that the StartDate parameter is used in a new StartString parameter. Additionally, an EndString parameter has been created that is using the EndDate parameter. This way, we can bind a calendar component directly to the Start and EndDate parameters and all the formatting will be done automatically in the report.

note

This format was used with a MySQL database, so your database may take a different format. Refer to your database's documentation for suggested date formats.

Once the new string parameters have been created, we can then reference them in the Basic SQL Query just like a normal string.

SELECT *
FROM group_table
WHERE t_stamp BETWEEN '{StartString}'
AND '{EndString}'