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:
View:
Tabel function:
Invantive Cloud SQL Editor
In the SQL Editor of Invantive Cloud the three types are also recognizable:
Table:
View:
Tabel 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:
Values can be provided to table function parameters by position or by name. Using positions requires all parameter values to be provided from the first one till the last one that needs to be specified. By name only some specific parameter can be assigned a value.
An example by position is:
select *
from BalancesEx@eol(10523, 2022, 3)
An example by name is:
select *
from BalancesEx@eol(Params_PrevYears => 3)
A combination of both position and name is also possible as in the following example where 10523 is assigned to the first parameter (Division) and 3 to the third one (Params_PrevYears):
select *
from BalancesEx@eol
( 10523
, Params_PrevYears => 3
)
Note that on platforms with partitioned data such as data per company, the query is executed once per selected company by specifying a different company_code, division or office_code. The list of selected companies can be specified using the use statement as described on Select companies/partitions for an Invantive Cloud database.
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 withselect * 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 withselect * 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:
Included pre-defined views can be found in the data dictionary view SystemViews:
select QUERY
from SYSTEMVIEWS@DataDictionary
where name = 'PayrollComponents'
Types of Views
There are two types of views within the Invantive products:
- provider-specific views; these are deployed and maintained by Invantive. They are not customizable by users and only combine data from a single data container.
- custom views; these are created by users, for example through the Startup SQL of a database. A custom view can combine data from multiple data containers. See also Using custom database views for SQL and BI tools.
Please bring up a forum topic if a new provider-specific view is desired. It is most convenient to add a custom view definition to it that can serve as the basis of the specification.
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:
- Oracle:
SELECT * FROM TABLE(f()) - SQL Server: inline table-value functions zoals
select * from myfunctionname(10, 'CODE') - SAP HANA:
select * from "SCHEMA"."TMP::WORKDAYS"('2003-01-01', '2003-02-01') - Teradata:
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.