In dit topic leer je wat tabelfuncties en tabelfunctieparameters zijn en hoe ze toegepast worden binnen Invantive UniversalSQL. In de UniversalSQL grammatica is meer informatie te vinden over de concepten achter Invantive UniversalSQL.
Invantive UniversalSQL 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:
View:
Tabelfunctie:
Invantive Cloud SQL Editor
In de SQL Editor van Invantive Cloud zijn de drie soorten ook herkenbaar:
Tabel:
View:
Tabelfunctie:
Verschil Tabellen en Views
Tabellen zijn geheel of grotendeels gegevens die 1-op-1 afkomstig zijn uit de achterliggende datacontainer. De Invantive UniversalSQL-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 UniversalSQL 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:
Waarden kunnen aan tabelfunctieparameters worden gegeven op positie of op naam. Als posities worden gebruikt, moeten alle parameterwaarden worden opgegeven, van de eerste tot de laatste die moet worden opgegeven. Op naam kan aan alleen een specifieke parameter een waarde worden toegekend.
Een voorbeeld op positie is:
select *
from BalancesEx@eol(10523, 2022, 3)
Een voorbeeld op naam is:
select *
from BalancesEx@eol(Params_VorigeJaren => 3)
Een combinatie van zowel op positie als op naam is ook mogelijk zoals in het volgende voorbeeld waarbij 10523 is toegewezen aan de eerste parameter (Deling) en 3 aan de derde (Params_PrevYears):
select *
from BalansenEx@eol
( 10523
, Params_PrevYears => 3
)
Merk op dat op platformen met gepartitioneerde gegevens zoals gegevens per bedrijf, de query één keer wordt uitgevoerd per geselecteerd bedrijf door een andere company_code, division of office_code op te geven. De lijst van geselecteerde bedrijven kan worden gespecificeerd met het use statement zoals beschreven op Administratie(s) selecteren voor een Invantive Cloud database.
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 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
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 UniversalSQL.
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:
De meegeleverde views zijn te vinden in de data dictionary view SystemViews:
select QUERY
from SYSTEMVIEWS@DataDictionary
where name = 'PayrollComponents'
Soorten Views
Er zijn twee soorten views binnen de Invantive producten:
- provider-specifieke views; deze worden door Invantive opgevoerd en onderhouden. Ze zijn niet aanpasbaar door gebruikers en combineren uitsluitend gegevens van een enkele datacontainer.
- eigen views; deze worden door gebruikers gemaakt, bijvoorbeeld via de Startup SQL van een database. Een eigen view kan data uit meerdere datacontainers combineren. Zie ook Eigen database-views gebruiken voor SQL en BI-tools.
Gelieve een forums topic op te voeren indien een nieuwe provider-specifieke view gewenst wordt. Het handigste is om dan alvast een eigen view definitie hieraan toe te voegen die als basis kan dienen van de specificatie.
Prestatie van tabelfuncties in views
Views die herhaaldelijk een tabelfunctie aanroepen met verschillende parameters uit een gewone tabel kunnen behoorlijk traag zijn. Vooral op platformen met een beperkte lage aanroepsnelheid zoals Teamleader kan het opvragen van alle data uit een view erg traag zijn. Bijvoorbeeld op Teamleader kan slechts 1 rij per seconde worden opgevraagd met behulp van de V1Flat en V2Flat views als gevolg van call rate beperkingen en het ontbreken van alternatieven die meer dan één rij per call teruggeven.
Tabelfuncties op Andere Platformen
Andere high-end SQL-platformen kennen ook concepten vergelijkbaar met de Invantive UniversalSQL-tabelfuncties:
- 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))
De syntax is soms anders dan Invantive UniversalSQL, maar de basis blijft dat een functie-aanroep met parameter gebruikt kan worden als databron.