Selecting partitions/administrations via the use SQL statement

Wiki (en)
, ,
1

Go to Dutch version

A data container can contain multiple partitions. On Exact Online, Yuki, Twinfield and the like, there is one partition per administration/company. On other platforms, partitions may be defined differently such as a database on a SQL Server or PostgreSQL instance, or a payrolled business on NMBRS or Loket.

SQL statements are executed on all selected partitions. The execution is usually in parallel with an adjustable degree of parallelism.

In most Invantive products the partitions can be selected through a user interface, for example through a drop-down menu in the ribbon.

It is also possible to use a SQL statement to select the partitions on which future queries and operations should run. The use statement allows you to select the partitions/administrations that all subsequent SQL statements will work on.

The most common variations of the use statement are:

  • use all: select all partitions/administrations,
  • use default: select the partitions/administrations that were last used in the (cloud)platform itself (if defined),
  • use 23423,345345: select by code a number of partitions/administrations (for example division code on Exact Online),
  • use select code, 'eol' from systemdivisions@eol where name like '%Drenthe%': select all partitions/administrations with a query, in this case on the Exact Online data container eol all administrations with “Drenthe” in the name.

Exclude

To exclude some companies, you can use a construct such as:

use
select code
,      'eol'
from   SystemDivisions@eol
where  code not in (DIVISIECODE1, DIVISIECODE2, DIVISIECODE3, ...)

More information

For more information: Invantive SQL Grammar v22.1

2

2 berichten zijn gesplitst naar een nieuw topic: Not able to select partitions