One of the most familiar questions at our support desk is “what functions are available” in Invantive UniversalSQL 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.