SIGNAL SELECT statement and clauses

In this section, it is explained how to use the SELECT statement and clauses in SIGNAL.

SELECT statement

The SELECT statement is used to select data from a process. It fetches data from columns and rows from the table. The data returned is the result set.

Syntax:

SELECT expressions
FROM table
[TABLESAMPLE]
[WHERE]
[GROUP BY]
[UNION ALL]
[ORDER BY]
[FILL]
[LIMIT]
[OFFSET]	

Parameter	Description
expressions	The columns or calculations that you want to retrieve.
table	The process table or view from which you want to retrieve data. You can reference the process by using the explicit Process ID which can be found on the API tab in the process settings page. Alternatively you can use the alias THIS_PROCESS to refer to the default view.
TABLESAMPLE	Specify the absolute or percentage table fraction to be considered for the query.
WHERE condition	The condition that must be met for the records to be selected. If no condition is provided, then all records are selected.
ORDER BY index	The index of a selected expression used to sort the records in the result set. If more than one expression is provided, separate the values with a comma. ASC sorts the result set in ascending order by expression, DESC sorts it in descending order. NULLS FIRST sorts the result set with null values first, NULLS LAST with null values last.
GROUP BY	Collects data across multiple records and group the results by one or more columns. The GROUP BY clause requires an index similar to the ORDER BY clause. You can use one or multiple indices.
UNION ALL	Concatenates the result sets of two or more SELECT statements.
FILL	Function to fill results.
LIMIT	The number of records in the result set.
OFFSET	The starting point to return rows from a result set.

Example:

SELECT column1, column2
FROM table

Parameter	Description
column1, column2	The columns from which you want to retrieve records.
table	The process table or view from which you want to retrieve records, referenced by explicit Process ID or the alias THIS_PROCESS.

This query returns the data of column1 and column2 from the specified table.

Only little effort is required to select multiple attributes and order and limit the result set.

Example:

SELECT case_id, Status, "Customer ID"
FROM table
ORDER BY 2 DESC
LIMIT 10

This query returns the case ID, status, and customer ID of the first 10 cases in the table. ORDER BY 2 DESC indicates to order the result set by the second column, which is Status, in descending order.

Subqueries

A subquery is a query that is nested inside a SELECT statement or inside another subquery. You can use a subquery anywhere an expression is allowed to retrieve data on event-level.

Subqueries are allowed in the FROM clause as well as in the FLATTEN operator.

Syntax:

SELECT (
SELECT(event_name))
FROM table

Parameter	Description
event_name	The column or expression from which you want to retrieve the first element.
table	The process table or view from which you want to retrieve records, referenced by explicit Process ID or the alias THIS_PROCESS.