Het volgende script is een voorbeeld om relatiecodes in Exact Online massaal bij te werken. Maak altijd VOORAF een backup en test het uitgebreid op een kopie vooraf.
Looptijd is circa 300 ms per bijgewerkte relatie in Exact Online.
local remark Source file in some arbitrary CSV format.
local remark In this case: human company number, old number, name, some audit, new number.
local define CSV_FILE "c:\temp\omnummeringtabel.csv"
--
-- Create a table mapping old account numbers of an
-- Exact Online company to a new (non-used) account number.
--
create or replace table mapping@inmemorystorage
as
select sdn.code division_code
, csv.*
from read_file_text('${CSV_FILE}')@Os rft
join csvtable
( passing rft.file_contents
column delimiter ';'
skip lines 0 /* There is no header row. Change to 1 when there is a header row. */
columns adm_nummer integer position 1 /* Use "next" instead of 1,2,3 on v20.1 */
, oud_nummer varchar2 position 2
, naam varchar2 position 3
, datum_aangemaakt datetime position 4
, aangemaakt_door varchar2 position 5
, nieuw_nummer varchar2 position 6
)
csv
join systemdivisions sdn
on sdn.hid = csv.adm_nummer
begin
--
-- Select all companies which have an account to be remapped.
--
use select distinct division_code from mapping@inmemorystorage;
--
-- One-by-one update the accounts.
--
-- Precondition: the new numbers are not in use as an old number.
--
for r in
( select *
from MAPPING@InMemoryStorage mpg
order
by mpg.division_code
, mpg.oud_nummer
)
loop
update ExactOnlineREST..Accounts
set code = to_char(r.nieuw_nummer)
where code = to_char(r.oud_nummer)
and division = r.division_code
and r.nieuw_nummer != r.oud_nummer
;
if sqlrowcount = 0
then
raise_application_error
( -20163
, 'Could not update account from '
|| to_char(r.oud_nummer)
|| ' to '
|| to_char(r.nieuw_nummer)
|| ' in division '
|| to_char(r.division_code)
|| ' because the old number does not exist.'
);
end if;
end loop;
end;
waarbij in de eerste kolom het administratienummer staat zoals linksboven in Exact Online getoond wordt. De divisioncode wordt er automatisch bijgezocht door de join op SystemDivisions.
Een bijpassend CSV-bestand met kopregel ziet er als volgt uit:
Een CSV-bestand met kopregel werkt alleen als de skip lines 0 in skip lines 1 veranderd wordt: de CSV-kopregel voor het omnummeren moet overgeslagen worden.
Bij het uitvoeren van het script op Exact Online krijg ik de foutmelding:
itgensql375: The value ‘Code_oud; Code_nieuw’ for ‘adm_nummer’ in column 1 and row 1 can not be mapped to a int32.
itgendid048: The value ‘Code_oud; Code_nieuw’ is not a valid integer. Use comma ‘,’ as a thousands separator.
De indeling van de invoertekenreeks is onjuist.
Het verwachte CSV-formaat voor het omnummeren bevat geen kopregel. Advies is om de kopregel te verwijderen in de kladblok of notepad++. Het voorbeeld is uitgebreid met skip lines 0 en een voorbeeld CSV zodat duidelijker is dat er geen kopregel verwacht wordt.
Vanochtend wat uitgebreider proberen te testen met de aangepaste code, alleen ontstaat er nu een andere foutmelding itgenisr34:
Table ‘MAPPING’ does not exist.
Zie onder de foutmelding en de gebruikte code.
local define CSV_FILE "C:\Export\Testaccounts.csv"
select *
from read_file_text('${CSV_FILE}')@Os rft
--
-- Create a table mapping old account numbers of an
-- Exact Online company to a new (non-used) account number.
--
create or replace table mapping@inmemorystorage
as
select sdn.code division_code
, csv.*
from read_file_text('${CSV_FILE}')@Os rft
join csvtable
( passing rft.file_contents
column delimiter ';'
skip lines 1
columns adm_nummer integer position 1
, Code_oud varchar2 position 2
, Code_nieuw varchar2 position 3
)
csv
join systemdivisions sdn
on sdn.hid = csv.adm_nummer
begin
--
-- Select all companies which have an account to be remapped.
--
use select distinct division_code from mapping@inmemorystorage;
--
-- One-by-one update the accounts.
--
-- Precondition: the new numbers are not in use as an old number.
--
for r in
( select *
from MAPPING@InMemoryStorage mpg
order
by mpg.division_code
, mpg.Code_oud
)
loop
update ExactOnlineREST..Accounts
set code = to_char(r.Code_nieuw)
where code = to_char(r.Code_oud)
and division = r.division_code
;
if sqlrowcount = 0
then
raise_application_error
( -20163
, 'Could not update account from '
|| to_char(r.Code_oud)
|| ' to '
|| to_char(r.Code_nieuw)
|| ' in division '
|| to_char(r.division_code)
|| ' because the old number does not exist.'
);
end if;
end loop;
end;
De foutmelding itgenisr034 treedt op als een in-memory tabel niet bestaat. Vermoedelijk is het statement met create or replace table mapping@inmemorystorage niet uitgevoerd en is gebruik gemaakt van CtrlEnter om alleen het laadstatement uit te voeren.
Voer alle statements uit (Execute All in menu of toets F5). De verwachting is dat dan wel lukt.
De regels met deze inhoud hebben geen functie lijkt het:
select *
from read_file_text('${CSV_FILE}')@Os rft
Dank weer! Met F5 was het inderdaad opgelost. Ik gebruikte al die tijd de optie “Uitvoeren”, zie onder. De optie “Execute All” kan ik niet vinden maar met F5 werkt het ook inderdaad.
