Skip to main content

Settings and Keywords

Keyword WITH​

Use WITH keyword to declare aliases, variables, expressions. You can use the declared elements in the rest of the query.

Examples with a keyword WITH
WITH
  entries AS array(L2EntryNew) AS 'l2',
  max(l2[side == BID].price) AS 'maxBid',
  min(l2[side == ASK].price) AS 'minAsk'
SELECT
  max{}(maxBid) AS 'highBid',
  min{}(minAsk) AS 'lowAsk'
FROM binance
OVER time(1m)
WHERE packageType == PERIODICAL_SNAPSHOT AND symbol == 'BTC/USDT'

Keyword TYPE​

Use keyword TYPE to set the type name of the query result. Can be used to map the query output to the specific class type.

Examples with a keyword TYPE
SELECT
  bbo[side == ASK].price AS "offerPrice",
  bbo[side == ASK].size AS "offerSize",
  bbo[side == BID].price AS "bidPrice",
  bbo[side == BID].size AS "bidSize"
TYPE "deltix.timebase.api.messages.BestBidOfferMessage"
FROM binance
ARRAY JOIN (entries AS array(L1Entry))[THIS IS NOT NULL] AS bbo

Keyword FIELD​

You can use the FIELD keyword to assign names to columns in the SELECT expression.

tip

It is important to differentiate column names that can be set using FIELD keyword from aliases set by AS keyword. You cannot have more than one alias with the same name, but with FIELD, you can assign the same names to more than one column - refer to the below example.

Refer to Alias to learn more.

SELECT
  RECORD 
    entry.price FIELD 'price' 
  TYPE t1 WHEN entry is L1Entry
  RECORD 
    entry.price FIELD 'price', 
    entry.size FIELD 'size' 
  TYPE t2 WHEN entry is L2EntryNew
FROM binance
ARRAY JOIN entries AS entry

Another useful example of the FIELD keyword usage is when you need to override the output field names that coincide with the input field names. This helps avoid naming conflicts and improves the clarity of your query results.

In the example below, we use entries and packageType as input parameters for orderbook. However, we also need to use entries and packageType as output field names:

WITH
  orderbook{maxDepth: 20}(packageType, entries) AS book
SELECT 
  book FIELD entries, 
  PERIODICAL_SNAPSHOT FIELD packageType 
TYPE "deltix.timebase.api.messages.universal.PackageHeader"
FROM "BINANCE"
OVER TIME (1m)
WHERE symbol == 'BTC/USDT'

Keyword THIS​

Use keyword THIS to reference the current message as object.

Examples with a keyword THIS
-- returns the entire message as object as a single field
SELECT THIS FROM binance

-- returns entries array from the current message
SELECT THIS.entries FROM binance

Case and Special Characters​

Identifiers in QQL are not case sensitive and get converted to upper case in case not embraced in double quotes. The following rule applies:

  • test = Test = TEST
  • "test" != "Test" != "TEST"

You do not have to embrace identifiers in quotes, but mind the following use cases:

  • use double quotes with identifier's name in case it includes special characters
  • use double quotes with identifier's name in case it starts with a numerical

QQL processor performs case-insensitive matching of fields, classes, streams.

QQL supports backslash quotation, such as \', \", \\, \n, \r, \t, \b, \f.

Example: s4'2 in QQL query write as 's4\'2'

Keywords LIMIT and OFFSET​

LIMIT keyword sets the number of records returned by the query. For example, adding limit 10 to the query will return just 10 records. OFFSET keyword applies to LIMIT and sets the starting record number. For example, offset 100 added to the query will return all records starting from the 101th record (when counting from 1).

Follow these rules to add LIMIT and OFFSET to QQL queries:

  1. limit <limit count>
  2. limit <limit count> offset <offset count>
  3. limit <offset count>, <limit count>

As a use case example, a combination of LIMIT and OFFSET can be applied to creating pagination.

Examples of LIMIT and OFFSET keywords
SELECT RUNNING entries, count{}() FROM kraken
LIMIT 10 OFFSET 5

SELECT RUNNING entries, count{}() FROM kraken
LIMIT 10, 15
tip

Please note, that a query with the OFFSET will still read all the records on the server side and return just the records considering the specified offset.