Create a lookup table of EU-countries

A frequent need is to have a temporary table for lookup-purposes. Often, the temporary need and data volume do not justify storage in a relational database. For this purpose Invantive SQL offers temporary in-memory tables which can be easily populated using for instance the csvtable or xmltable source.

The following code create a table eu_countries with all EU-members (excluding Great-Britain):

create or replace table eu_countries@inmemorystorage
select *
from   csvtable
         row delimiter ','
         column delimiter '#'
         columns country_code varchar2 position next

The in-memory table with EU-countries has the following contents:

Table of EU-countries

This table can be used for instance to correct VAT-codes in Invantive Estate such as:

-- Customer outside NL, but within EU: VAT code 7
update bubs_leveranciers_v@ora
set    bce_code = '7'
where  lvr_land in ( select country_code from eu_countries@inmemorystorage )
and    lvr_land != 'NL'
and    bce_code is null

Since the number of elements in the IN-clause is little and in-memory tables are considered very fast, the IN will automatically be rewritten to an explicit list of values. This enables the use of server-side filtering and the use of indexes on the target platform.

A similar sample for updating Exact Online account codes using csvtable is available on Massaal bijwerken relatiecodes in Exact Online naar specifieke nieuwe relatiecodes.

More information on the use and performance advantages of server-side filtering is available on How to filter data from OData query on Azure Data Factory? - #3 door guido.leenders.