Skip to main content

EqualTo SQL

  • Updated

Introduction

EqualTo SQL is used to transform and combine data, and supports a subset of PostgreSQL. This document defines exactly what is and is not supported by EqualTo SQL.

 

How is EqualTo SQL used?

EqualTo SQL is used to define an "SQL Data Table" on EqualTo. For information on how to create and manage SQL data tables, please see our Data Tables article.

An SQL Data Table is a structured table of data, which is defined as a query against your other data tables. For example:

  • Data Table Name: SalesWithInvoiceAmount
  • EqualTo SQL: SELECT Sale.id, Sale.owner, Sale.arr, Sale.date, Invoice.amount WHERE Sale.invoice = Invoice.id;
  • Contents of SalesWithInvoiceAmount: 

Sale.id

Sale.owner

Sale.arr

Sale.date

Invoice.amount

123

John Doe

€8,000

2022-Jan-23

€9,200

456

John Doe

€7,900

2022-Jan-17

€8,500

 

Typically, an SQL data table would then be processed by an incentive plan, so that commissions could be calculated for each row of consolidated data:

 

Sale.id

Sale.owner

Sale.arr

Sale.date

Invoice.amount

Reward

123

John Doe

€8,000

2022-Jan-23

€9,200

€920

456

John Doe

€7,900

2022-Jan-17

€8,500

€850

 

Syntax

EqualTo SQL supports only SELECT queries, as well as the following keywords: AND, AS, BY, CASE, ELSE, END, FALSE, FROM, GROUP, IS, NOT, NULL, OR, SELECT, THEN, TRUE, UNION, WHEN & WHERE.

Standard SELECT statement syntax is supported:

SELECT expressions

[FROM tables]

[WHERE condition]

[GROUP BY expressions]

 

Some things to note:

  • EqualTo SQL does not allow "*" selects.
  • We support all JOIN operators
  • WHERE condition can be expressed as a list of comparisons (=, <>, <, <=, >, >=, IS or IS NOT) linked by logical operators (AND or OR).
  • HAVING clause is not supported yet.
  • General UNION operator is supported (select_statement1 UNION select_statement2).

 

Examples:

SELECT NAME FROM USER;

OK

SELECT NAME, AGE FROM USER;

OK

SELECT * FROM USER;

BAD

You cannot use SELECT *

UPDATE USER SET NAME="TEST";

BAD

You cannot UPDATE rows

DELETE FROM USER WHERE USERID=1;

BAD

You cannot DELETE rows

 

Types

Supported types: BOOLEAN, DATE, DATETIME (stored as TIMESTAMP WITH TIME ZONE), INTEGER, NUMBER (stored as DOUBLE PRECISION) & TEXT.

 

Types are strict, without implicit casting - the types in the expressions need to match perfectly.

- it is not possible to compare, for example, DATE with TEXT or even INTEGER with NUMBER;

- it is not possible to do SELECT 1 UNION SELECT 1.5.

This is different from the PostgreSQL behavior which always tries to cast the values.

 

Every cell in SQL data tables is nullable but the type of each of the output columns needs to be unambiguous.

 

Examples:

SELECT NULL;

BAD

It's not clear what the type of the column is, which is not acceptable in EqualTo SQL.

SELECT NULL UNION SELECT 1;

OK

The column is of type INTEGER.

SELECT 1 UNION SELECT 1.5;

BAD

You can't UNION an INTEGER with a NUMBER, and EqualTo SQL does not support implicit casting.

SELECT 1 WHERE DATE('2020-01-01') < '2020-01-02';

BAD

You can't compare date with text

SELECT 1 WHERE 2.5 >= 2;

BAD

You can't compare number with integer

Constraints

Every SQL data table is required to have a primary key. EqualTo SQL does support tables which contain composite primary keys (where the primary key is a combination of columns). Note:

  • If your EqualTo SQL query produces a row with NULL in a primary key value, that row will not be included in the SQL data table.
  • If your EqualTo SQL query produces multiple rows with the same primary key, then all but one of those rows will be omitted.

We do not support any other constraints at the moment.

 

Functions

EqualTo SQL supports the following functions:

 

  • DATE
  • ADDMONTHS
  • DATE_TRUNC
  • ROUND
  • LEAD
  • SUM
  • COUNT
  • MIN
  • MAX 
  • AVG

We statically verify, where possible, that a function supports the values being passed into it. In some situations, static verification is unable to catch a type mismatch (eg: calling DATE(column) while not all values in the column can be converted to a date). In such a case, we return NULL as a fallback. Note that this is inconsistent with the default PostgreSQL behavior where such statements would fail.

 

Arithmetic Operators

EqualTo supports the following operations: +, -, *, /

 

 

Conditional expressions

Supported CASE syntax:

 

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    …

    ELSE else_result

END

 

Please note that, unlike in postgres, ELSE clause is not optional.

Coming Soon

There are many PostgreSQL features not yet supported by EqualTo. Some notable omissions:

  • Type conversions
  • COALESCE
  • Subqueries
  • Array comparisons (i.e. IN operator)
  • UNION ALL

 

If you particularly need some PostgreSQL capability, contact support@equalto.com and we'll try to prioritize your request.