What are table functions and table function parameters?

Go to Dutch version

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.

Invantive Query Tool

In the Invantive Query Tool, the three types are identified by the icon in front of the name:

Table:
Table

View:
View

Tabel function:
Tabelfuncton

Invantive Cloud SQL Editor

In the SQL Editor of Invantive Cloud the three types are also recognizable:

Table:
Table

View:
View

Tabel function:
Table function

Difference Tables and Views

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.

Table Functions

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.

Why do Table Functions Exist?

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')

Table Function Parameters

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:

Table function parameters of Accountancy Profiles

Documentation

The table function parameters can also be found in the online documentation of the respective platform, as here for the Teamleader table function ContactById:

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.

Data Dictionary

Alternatively, this information can be retrieved from the data dictionary with the view SystemTableFunctionParameters:

select name
,      presence_required
,      database_data_type
,      dotnet_data_type
,      documentation
from   systemtablefunctionparameters@datadictionary
where  table_name = 'ContactById'
order
by     sorting_order

Tabel Functions and Power BI

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 and Power BI

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.

This view 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:

PayrollComponents

Included pre-defined views can be found in the data dictionary view SystemViews:

select QUERY
from   SYSTEMVIEWS@DataDictionary
where  name = 'PayrollComponents'

Performance of Table Functions in Views

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 V1Flat and V2Flat views due to call rate limitations and lacking multi-row alternatives.

Table Functions on Other Platforms

Other high-end SQL platforms also have concepts similar to the Invantive SQL table functions:

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.