Print

WHEN

The WHEN clause indicates the conditions that must be met in order for the query to trigger its OUTPUT action.

It’s comparable to the “WHERE” clause in standard SQL, but we use “WHEN” to emphasize that this query is not running just once. It’s getting deployed to re-run indefinitely and it will trigger an action when conditions are met.

The WHEN clause is optional. If not specified, then every message received from the stream is considered a match, and the output action will be triggered every time (unless the query has been suspended by SLEEP clause, of course).

What can be referenced in conditions?

  1. Any field from the current message that is passing through the stream, like: my_stock_stream.SYMBOL = ‘TSLA’ You can use just the field name if it’s not found on multiple sources, like: SYMBOL = ‘TSLA’ You cannot access fields from past messages. But all fields from the current message are accessible.
  2. Any aggregation value from any window in the query data sources (FROM), like: last_10_bids.SUM > 100 You can just use the aggregation name if it’s not found on multiple sources, like: SUM > 100 You cannot select an aggregation from a window that is not running that aggregation.
  3. Expressions, for example: (SUM – MAX) / 2 > 100
  4. Scalar functions, like: LENGTH ( SYMBOL ) = 4
  5. Hardcoded values, like: 400 < 500

Syntax

The WHEN clause accepts a condition expression controlled by the following operators:

OperatorPurpose
=Equal to
!Not
>Greater than
<Less than
>=Greater than or equal to
<=Less than or equal to
LIKEString contained in other string plus wildcards. For example, ‘SPY’ LIKE ‘S%’ returns true.
IN()String contained in list of strings. For example, ‘SPY’ IN (‘TSLA’,’APPL’,’SPY’) returns true.
ANDAn evaluation AND the next evaluation must both be true.
OREither an evaluation OR the next evaluation are true.
()Evaluation groups. (( a > 0) OR (b < 0 AND c = 0 )

Condition evaluations can reference any fields from the current message passing the stream.

WHEN <condition expression>

Example

Newlines are ignored by usefule for code readability:

SELECT ...
FROM ...
WHEN   my_stock_stream.SYMBOL = 'TSLA'
AND    last_10_bids.SUM > 100
AND    ( last_10_bids.COUNT * 5 > last_10_bids.SUM 
     OR  LENGTH ( my_stock_stream.SYMBOL) != 4)

If the FROM clause provides data source aliases:

SELECT ...
FROM ...
WHEN   s.SYMBOL = 'TSLA'
AND    b.SUM > 100
AND    ( b.COUNT * 5 > b.SUM 
     OR  LENGTH ( s.SYMBOL) != 4)

Note, there are no JOINs in RioDB. No inner join, no outer join. Why? In RioDB, all data sources contain only one record. Each stream message is just one single record. And each window is also just one single record of aggregated fields. In the end, the ‘cartesian product’ of selecting data from multiple sources always boils down to just one output record. RioDB queries are not meant to find a bunch of records that match certain conditions. Instead, it’s meant to check if the current state of data streams match certain conditions. It’s either “yes” or “no”. Either the state of the streams matches your query, or it doesn’t.

Table of Contents
Scroll to Top