Invantive SQL Grammar and Exact Online

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

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:

  • Free version: second-generation SQL parser without joins and some upcoming non-ANSI standard advanced mapping functions for large volume financial analysis and reporting.
  • 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.