Restrict list of employees in NMBRS to just a few companies

Question

I am creating a report on sick leave on nmbrs.nl using Invantive SQL.

By default this query retrieves data across all companies:

select *
from   employees emp
join   employeeabsence(emp.id)

This takes an enormous amount of time since for each company a SOAP request is done, plus one SOAP request per employee to retrieve the absence.

Is there an efficient way to restrict it to just a few companies instead of thousands?

Answer 1

You can use the 'use' statement or select a partition which is actually a company.

With use you can use a query like:

use select code from systempartitions@datadictionary where  lower(name) like '%companyname%' limit 10

to retrieve the first 10 companies with a specific name.

Also see answer on use with alias on how to also specify the data container alias when running distributed queries.