Select multiple Exact Online administrations based upon query

Question

When you have many partitions (Exact Online administrations) like 2500, it can become quite cumbersome to select the right companies out of this list one-by-one and then copy & paste it into a large use COMPANY1, COMPANY2, COMPANY3, ... statement.

How can I more efficiently select a number of Exact Online administrations?

Answer 1

There are two more efficient ways to select a larger list of Exact Online companies from the available ones.

With [listagg][1] you can create a string that contains all relevant companies, such as for all companies associated with your office in the city of Amersfoort:

select listagg(divisionclasses_divisionclassname_divisionclassnames_administration_code_attr ) 
from   ministrationclassifications 
where  divisionclasses_divisionclassname_divisionclassnames_administration_currency_code_attr = 'EUR' 
and    divisionclasses_divisionclassname_description = 'Vestiging' 
and    description = 'Amersfoort'

And then copy & paste the output after the use statement.

In more recent versions of Invantive SQL you can also use the following syntax:

use select divisionclasses_divisionclassname_divisionclassnames_administration_code_attr from ministrationclassifications where divisionclasses_divisionclassname_divisionclassnames_administration_currency_code_attr = 'EUR' and divisionclasses_divisionclassname_description = 'Vestiging' and description = 'Amersfoort'

This selects all companies associated with your office in the city of Amersfoort and EURO as currency.