Wat zijn tabelfuncties en tabelfunctieparameters?

In dit topic leer je wat tabelfuncties en tabelfunctieparameters zijn en hoe ze toegepast worden binnen Invantive SQL. In de SQL grammatica is meer informatie te vinden over de concepten achter Invantive SQL.

Invantive SQL kent drie soorten tabellen:

  • tabellen: een lijst van rijen met gegevens die opvraagbaar zijn door de naam op te geven
  • views: een lijst van rijen met gegevens die opvraagbaar zijn door de naam op te geven
  • tabelfuncties: een lijst van rijen met gegevens die opvraagbaar zijn door de naam op te geven in combinatie met waardes voor parameters

Tabellen en views hebben in het algemeen een Engelse naam in de meervoudsvorm zoals Projects. Tabelfuncties hebben in het algemeen een Engelse naam in de meervoudsvorm met als achtervoegsel By gevolgd door de meest belangrijke parameters.

Invantive Query Tool

In het Invantive Query Tool zijn de drie soorten herkenbaar aan het ikoon voor de naam:

Tabel:
Tabel

View:
View

Tabelfunctie:
Tabelfunctie

Invantive Cloud SQL Editor

In de SQL Editor van Invantive Cloud zijn de drie soorten ook herkenbaar:

Tabel:
Tabel

View:
View

Tabelfunctie:
Tabelfunctie

Verschil Tabellen en Views

Tabellen zijn geheel of grotendeels gegevens die 1-op-1 afkomstig zijn uit de achterliggende datacontainer. De Invantive SQL drivers sturen voor de inhoud van een tabel een verzoek volgens het protocol dat de datacontainer verwacht en krijgen de gegevens terug. Soms worden een beperkt aantal kolommen toegevoegd met afgeleide gegevens, zoals DivisionLabel op Exact Online.

Views hebben dezelfde structuur als tabellen, maar de inhoud wordt afgeleid met een SQL-query op basis van tabellen en andere views. De inhoud van elke view komt dus uiteindelijk tot stand door de inhoud van tabellen te combineren.

Een voorbeeld van een eenvoudige view op basis van een tabel 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%'

Deze view zou bij een rekeningschema volgens Brouwer alleen de unieke ID en de code tonen van de balansposten tonen.

Tabelfuncties

Een tabelfunctie is een vreemde eend in de bijt.

Een tabelfunctie is geen verzameling (“set”) van gegevens in de traditionele interpretatie; een tabelfunctie herbergt een groot aantal verzamelingen van gegevens. Door waardes voor parameters op te geven zoals bij een wiskundige functie komt pas een verzameling gegevens beschikbaar voor een query.

De verzamelingen die terugkomen bij verschillende combinaties van parameters kunnen disjunct zijn, overlappen of geheel identiek zijn zoals de ontwerper van de achterliggende functie dat nodig acht.

Waarom zijn er Tabelfuncties?

Tabelfuncties worden in Invantive SQL veel gebruikt om ook gegevens uit meer procedureel georiënteerde API’s beschikbaar te stellen voor verwerking met SQL.

Zo kent bijvoorbeeld Teamleader zowel in hun V1 als V2 API’s een API om van één specifieke contactpersoon alle velden op te vragen, inclusief de waardes van custom fields. Voor Teamleader V1 gaat dat met:

-- Vraag eerste de beste ID op (in Teamleader V1 is dat een decimaal getal).
select *
from   v1.contacts
limit  1

select *
from   v1.contact(10814620)

En voor Teamleader V2 API’s met:

-- Vraag eerste de beste ID op (in Teamleader V2 is dat een GUID).
select id
from   v2.contacts
limit  1

select *
from   v2.contactbyid('57da4314-d89a-0d9f-8771-f8c921391d74')

Tablefunctieparameters

Elke tabelfunctie heeft één of meer parameters waarvoor waardes opgegeven kunnen worden. De parameters kunnen verschillende datatypes hebben, zoals getallen, datums, BLOB’s of teksten.

De parameterwaardes kunnen geheel of deels verplicht zijn en er kunnen onderlinge relaties zijn tussen de parameterwaardes zoals startDate < endDate.

De lijst en documentatie van de tabelfunctieparameters is op te vragen in het Invantive Query Tool door de tabelfunctienaam te selecteren zoals in onderstaand voorbeeld van de Accountancy Profiles API van Exact Online:

Tabelfunctieparameters van Accountancy Profiles

Documentatie

De tabelfunctieparameters zijn ook te vinden in de online documentatie van het desbetreffende platform, zoals hier voor de Teamleader tabelfunctie 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

Ook kan deze informatie opgevraagd worden in het data dictionary met de view SystemTableFunctionParameters:

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

Tabelfuncties en Power BI

Tabelfuncties waarbij tenminste één parameter verplicht een waarde moet hebben worden op dit moment niet beschikbaar gemaakt voor Power BI omdat Power BI nog geen handige mogelijkheden heeft om de parameterwaardes op te geven en/of te itereren over grote aantallen combinaties van parameterwaardes. Ervaren Power BI consultants met Invantive kennis kunnen zelf de gegevens opvragen, bijvoorbeeld via een in-memory table.

Views en Power BI

Op veel tabelfuncties zijn voor gebruik met Power BI of Data Replicator views toegevoegd zoals bijvoorbeeld PayrollAdministrationPayrollRunPeriodResults op Loket. De views geven de meest voorkomende informatie terug, maar hebben als nadeel dat ze lang kunnen duren omdat ze mogelijk honderdduizenden API calls moeten uitvoeren. Voor grote volumes is het daarom nodig om de queries te optimaliseren, maar het wordt aangeraden deze stap pas te zetten zodra duidelijk is dat de view de benodigde informatie ook zeker bevat.

Sommige platformen leunen dicht aan tegen de relationele theorie en bieden gegevens aan in een formaat waarbij filters doorgegeven kunnen worden en geen iteratie nodig is over alle mogelijkheden. Andere platformen hebben een meer traditionele aanpak van API’s, bijvoorbeeld doordat de API’s gegroeid zijn uit bestaande oplossingen van specifieke praktijkproblemen, en hebben daardoor heel veel tabelfuncties en weinigen tabellen op Invantive SQL.

Voor Power BI en Invantive Data Replicator is het wenselijk dat de gegevens in tabelformaat beschikbaar is. Eventueel beschikbare views maken dat gemakkelijker. Een voorbeeld hiervan is de view PayrollComponents op Exact Online Salaris.

Deze view PayrollComponents itereert overall dienstverbandconditiegroepen en haalt de bijbehorende salariscomponenten op:

select pct.*
from   EmploymentConditionGroups ecp
join   ExactOnlineREST..PayrollComponentsByEmploymentConditionGroupCode(ecp.Division, ecp.Code) pct

De gegevens zijn dan meteen beschikbaar doordat de view ExactOnlineREST.Payroll.PayrollComponents geen parameters heeft:

PayrollComponents

De definities van meegeleverde views zijn te vinden in de data dictionary view SystemViews:

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

Tabelfuncties op Andere Platformen

Andere high-end SQL-platformen kennen ook concepten vergelijkbaar met de Invantive SQL tabelfuncties:

De syntax is soms anders dan Invantive SQL, maar de basis blijft dat een functie-aanroep met parameter gebruikt kan worden als databron.