In this topic you will learn what table functions and table function parameters are and how they are applied within Invantive SQL. In the SQL grammar you can find more information about the concepts behind Invantive SQL.
Invantive SQL has three types of tables:
- tables: a list of rows with data that can be retrieved by providing the name
- views: a list of rows with data that can be accessed by providing the name
- Table functions: a list of rows with data that is retrievable by providing the name in combination with values for parameters.
Tables and views generally have an English name in the plural form such as
Projects. Table functions generally have an English name in the plural form with the suffix
By followed by the most important parameters.
In the Invantive Query Tool, the three types are identified by the icon in front of the name:
In the SQL Editor of Invantive Cloud the three types are also recognizable:
Tables are all or mostly data that map 1-to-1 to the underlying data container. The Invantive SQL drivers send a request for the content of a table according to the protocol expected by the data container and gets the data in return. Sometimes a limited number of columns are added with derived data, such as
DivisionLabel on Exact Online.
Views have the same structure as tables, but the content is derived from an SQL query based on tables and other views. So the content of each view is ultimately created by combining the content of tables.
An example of a simple view based on a table is:
select gat.id , gat.code from exactonlinerest..glaccounts gat where gat.code not like '4%' and gat.code not like '5%' and gat.code not like '6%' and gat.code not like '7%' and gat.code not like '8%' and gat.code not like '9%'
This view would display according to Dutch accounting principles solely the unique ID and code of balance entries.
A table function is an oddity.
A table function is not a collection (“set”) of data in the traditional interpretation; a table function incorporates a large number of collections of data. By specifying values for parameters as in a mathematical function, only a specific collection of data is made available.
The collections returned by different combinations of parameters may be disjoint, overlapping, or completely identical as the designer of the underlying function sees fit.
Table functions are often used in Invantive SQL to make data available for processing with SQL from more procedurally oriented APIs.
For example, Teamleader has both in their V1 and V2 APIs an API to retrieve all fields of a specific contact, including the values of custom fields. For Teamleader V1, this is queried using:
-- Select a random ID (on Teamleader V1 a decimal number). select * from v1.contacts limit 1 select * from v1.contact(10814620)
And on Teamleader V2 APIs:
-- Select a random ID (on Teamleader V2 a GUID). select id from v2.contacts limit 1 select * from v2.contactbyid('57da4314-d89a-0d9f-8771-f8c921391d74')
Each table function has one or more parameters for which values can be specified. The parameters can have different data types, such as numbers, dates, BLOBs or texts.
The parameter values can be completely or partially mandatory and there can be interrelationships between the parameter values such as
startDate < endDate.
The list and documentation of the table function parameters can be retrieved in the Invantive Query Tool by selecting the table function name as in the example below from the Accountancy Profiles API of Exact Online:
The table function parameters can also be found in the online documentation of the respective platform, as here for the Teamleader table function
Table Function Parameters
The following parameters can be used to control the behaviour of the table function ContactById. A value must be provided at all times for required parameters, but optional parameters in general do not need to have a value and the execution will default to a pre-defined behaviour. Values can be specified by position and by name. In both cases, all parameters not specified will be treated using their default values.
Value specification by position is done by listing all values from the first to the last needed value. For example with
select * from table(value1, value2, value3)on a table with four parameters will use the default value for the fourth parameter and the specified values for the first three.
Value specification by name is done by listing all values that require a value. For example with
select * from table(name1 => value1, name3 => value3)on the same table will use the default values for the second and fourth parameters and the specified values for the first and third.
Alternatively, this information can be retrieved from the data dictionary with the view
select name , presence_required , database_data_type , dotnet_data_type , documentation from systemtablefunctionparameters@datadictionary where table_name = 'ContactById' order by sorting_order
Table functions that have at least one required parameter are currently not made available for Power BI because Power BI does not yet have convenient possibilities to specify the parameter values and/or iterate over large numbers of combinations of parameter values. Experienced Power BI consultants with Invantive knowledge can retrieve the data themselves, for example through an in-memory table.
Views have been added to many table functions for use with Power BI or Data Replicator, such as
PayrollAdministrationPayrollRunPeriodResults on Loket. The views return the most common information, but have the disadvantage that they can be lengthy because they may have to perform hundreds of thousands of API calls. For large volumes, it is therefore necessary to optimize the queries, but it is recommended to take this step only once it is clear that the view definitely contains the required information.
Some platforms lean close to relational theory and provide data in a format where filters can be passed and no iteration is required over all possibilities. Other platforms have a more traditional approach to APIs, for example because the APIs have grown out of existing solutions to solve then-current problems, and therefore have lots of table functions and few tables on Invantive SQL.
For Power BI and Invantive Data Replicator it is desirable that the data is available in table format. Available views simplify this process. An example is the view
PayrollComponents on Exact Online Payroll.
PayrollComponents iterates overall employment condition groups and retrieves the corresponding salary components:
select pct.* from EmploymentConditionGroups ecp join ExactOnlineREST..PayrollComponentsByEmploymentConditionGroupCode(ecp.Division, ecp.Code) pct
The data is then immediately available because the view
ExactOnlineREST.Payroll.PayrollComponents has no parameters:
Included pre-defined views can be found in the data dictionary view
select QUERY from SYSTEMVIEWS@DataDictionary where name = 'PayrollComponents'
Views which repeatedly call a table function with different parameters taken from a regular table can be quite sluggish. Especially on platforms with a limited low call rate like Teamleader retrieval of all data from a view can be very slow. For instance, on Teamleader only 1 row per second can be queried using the
V2Flat views due to call rate limitations and lacking multi-row alternatives.
Other high-end SQL platforms also have concepts similar to the Invantive SQL table functions:
SELECT * FROM TABLE(f())
- SQL Server: inline table-value functions zoals
select * from myfunctionname(10, 'CODE')
select * from "SCHEMA"."TMP::WORKDAYS"('2003-01-01', '2003-02-01')
select * from table(getStoreData(9005))
The syntax is sometimes different from Invantive SQL, but the basis remains that a function call with a parameter can be used as a data source.