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
as
select *
from csvtable
( passing 'SE,IM,CZ,ES,SK,SI,BE,BG,CY,DK,DE,EE,FI,FR,GR,HU,IE,IT,MC,HR,LT,LV,LU,MT,AT,PL,PT,RO'
row delimiter ','
column delimiter '#'
columns country_code varchar2 position next
)
The in-memory table with EU-countries has the following contents:
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 van forums.