Dit topic leert je hoe met Invantive SQL gemakkelijk een formulier kunt vullen met een lijst gegevens met een vast aantal rijen uit een database.
Met Invantive Control for Excel kun je rapportages en analyses maken op basis van Invantive SQL queries. Je kunt met Invantive Control for Excel ook formulieren vullen met gegevens uit tabellen van je database zoals bijvoorbeeld uit de Exact Online of AFAS Profit database. Uiteraard kan dat ook in Word met Invantive Composition for Word, maar veel professionals werken liever met Excel.
De zogenaamde “blokken” in de Excel add-in hebben een variabele lengte: voor alle rijen in de selectie wordt een Excel rij of kolom gevuld. De keuze voor groeien langs de X- of Y-as wordt gemaakt via de instellingen op het blok.
Voor sommige formulieren die op papier afgedrukt moeten worden gebruikt men een lay-out die vaak een blok met gegevens uit de database bevat tussen de formulierkop en een afsluitende tekst.
Een telkens terugkerende vraag is hoe je er voor zorgt dat het blok altijd een bepaald aantal rijen bevat, waarvan een deel leeg mag zijn. Een voorbeeld is een Excel formulier met daarin per artikel in een levering de specificaties van dat artikel of een certificaat met per eigenschap van een batch de meetwaardes.
In Excel kun je op blokniveau kun je geen vaste omvang qua aantal rijen instellen. Je kunt echter wel het aantal rijen automatisch aanvullen met een query in Invantive SQL. Een voorbeeld van zo’n SQL statement is:
select * except sorting_order
from ( select 1 sorting_order
, dummy_string
from dual@datadictionary
union all
select 2 sorting_order
, null dummy_string
from dual@datadictionary
join range(25)@datadictionary
)
order
by sorting_order
limit 25
In dit geval wordt in de linkerkant van union uit de dual tabel met precies 1 rij data opgehaald. Daarna worden hier precies 25 rijen toegevoegd door de range tabelfunctie. Na sortering op de tijdelijke kolom sorting_order worden de eerste 25 rijen teruggegeven aan Invantive Control.
Vervang de waarde 25 op twee plekken als je altijd een ander vast aantal rijen dan 25 wilt terugkrijgen.
Als de linkerkant van de union 5 rijen had teruggegeven, dan zou het eindresultaat nog steeds 25 rijen bevatten. De from geeft er 5+25 terug, maar na sortering blijven er maar 25 rijen over als resultaat van de query.
Als de SQL query aan de linkerkant complex is, dan kan het handig zijn om eerst een in-memory tabel te maken via een trigger op het Excel blok, bijvoorbeeld met:
create or replace table results@inmemorystorage
as
select ...
from ...
en vervolgens de gematerialiseerde query resultaten te gebruiken in de query op het Excel blok.