One of the most familiar questions at our support desk is "what functions are available" in Invantive SQL to query data in Exact Online.

First-generation SQL Parser of Invantive

The Exact Online XML provider offers a first-generation SQL parser from Invantive and solely supports:

select COLUMNS from TABLE-OR-VIEW where (filters with and/or) order by COLUMNS
Invantive SQL Grammar and Exact Online

Second-generation SQL Parser of Invantive

However, the second-generation SQL parser from Invantive is shipped with the Exact Online REST provider and the combined REST/XML Exact Online provider (selected sites only currently). This second-generation SQL parser is an extensive implementation of many commonly found SQL constructs from the ANSI SQL standard.

It includes in addition to the features of the first-generation SQL parser also:

  • joins,
  • outer joins,
  • cross joins,
  • group functions such as stddev, avg and listagg,
  • value functions such as xmlescape and round.

There are two flavours shipped:

1. Free version: second-generation SQL parser without joins and some upcoming non-ANSI standard advanced mapping functions for large volume financial analysis and reporting.

2. Paid version: identical to the free version but with joins and advanced mapping functions.

EBNF SQL Grammar

A full overview of the functions available is online available in familiar EBNF format at invantive.com. It includes extensive hyperlinks. I like to thank @Gunther Rademacher for making this graphical display possible.