'Use select' notation to specify multiple partitions of Exact Online data container

Question

I'd like to select an active partition in the Data Hub. Normally I would use the following statement:

INVANTIVE> use 1552839
2>
Exclamation itgendhb077: Error in Invantive Data Hub.
Database '1552839' does not exist. Make sure that the name is entered correctly.

That gives an error. I noticed when I use the alias for my Exact Online Connection it does work so:

INVANTIVE> use 1552839@eol
2>
itgendhb018: 0 Rows Affected (111 ms)

But I'd like to do the following, selecting the divisions I want from a SQL table, that doesn't work:

use select division_code from dc.rs.selected_admins@sql

An alternative doesn't work as well:

INVANTIVE> use select code from systemdivisions@eol sd inner join dc.rs.selected_admins@sql sa on sd.code = sa.division_code
2>
Exclamation itgendhb077: Error in Invantive Data Hub.
Database '1552839' does not exist. Make sure that the name is entered correctly.

Answer 1

When the alias is not specified, the first connected data container in terms of "sortingorder" is addressed. Given the type of error message, that is probably a SQL Server database.

Assuming that you are looking for a way to specify the partitions in a specific data container, the best approach is to use:

use select code partitioncode, 'eol' datacontaineralias from ...

This is identical to taking the outcome of:

select 'use ' || lst from ( select listagg(code || '@' || eol') lst from ... )

and executing that one.

Of course, when you have multiple data containers connected like Exact Online Belgium, the Netherlands and Loket and Nmbrs, you can provide them all in one use statement.