Uniek relatienummer vinden via SQL in Exact Synergy

In Exact Synergy worden relaties opgeslagen in twee aparte tabellen: DivisionDebitors voor debiteuren en DivisionCreditors voor crediteuren. Het veld voor het relatienummer heet Debtor in de tabel DivisionDebitors en Creditor in de tabel DivisionCreditors. Als je een nieuw uniek relatienummer wilt toewijzen dat in geen van beide tabellen al in gebruik is, kun je onderstaande SQL-query gebruiken.

De query

SELECT TOP 1 Numbers.Number AS BeschikbaarNummer FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM sysobjects a CROSS JOIN sysobjects b ) Numbers WHERE Numbers.Number NOT IN (SELECT Debtor FROM DivisionDebitors WHERE Debtor IS NOT NULL) AND Numbers.Number NOT IN (SELECT Creditor FROM DivisionCreditors WHERE Creditor IS NOT NULL) AND Numbers.Number > 0 ORDER BY Numbers.Number

Toelichting op de query

De subquery genereert een reeks opeenvolgende nummers door gebruik te maken van ROW_NUMBER() in combinatie met een cross join op sysobjects. Dit levert een aaneengesloten reeks gehele getallen op.

Vervolgens worden deze gegenereerde nummers gefilterd op nummers die nog niet voorkomen in het veld Debtor van DivisionDebitors en ook niet in het veld Creditor van DivisionCreditors. NULL-waarden worden uitgesloten via de WHERE-clausule op IS NOT NULL.

Met TOP 1 en ORDER BY Numbers.Number wordt het laagste beschikbare nummer teruggegeven.

Let op bij het bereik

De gegenereerde nummerreeks is afhankelijk van het aantal rijen in sysobjects. Als je organisatie relatienummers gebruikt die hoger zijn dan het aantal rijen in sysobjects, pas dan het bereik aan door een grotere cross join te gebruiken of een andere methode voor het genereren van nummers toe te passen.

Praktisch gebruik

Deze query is handig bij het handmatig aanmaken van relaties via SQL of bij het valideren van nummers voor een import. Controleer altijd of het voorgestelde nummer voldoet aan de nummerserie die jouw organisatie hanteert voordat je het nummer toekent aan een nieuwe relatie.