By adding the SKIP-GROUP-DUPLICATES option, the TENANT-WHERE option on a FOR EACH automatically guarantees that duplicate records that might otherwise be displayed for tenants that are in the same tenant group are only displayed once per group. The following examples illustrate how queries with a TENANT-WHERE option handle tenant-group data. They all assume that Customer is a multi-tenant table with at least one tenant group defined.
The following example is a simple query with a simple TENANT-WHERE option that selects one tenant in a group.
Super-tenant query for one tenant in a group
FOR EACH Customer WHERE cust-num < 100 TENANT-WHERE TENANT-ID = 3.
If the code in the above example is executed by a regular tenant, OpenEdge raises a run-time error.
If the code in the above example is executed by a super tenant, the user sees customers with cust-num < 100 for the tenant whose tenant ID is 3, or if tenant 3 is part of a group defined for Customer, they see the whole group's customers with cust-num < 100. The super tenant sees the same result regardless of their effective tenancy.
The following example shows a simple query with an compound TENANT-WHERE option that selects for multiple tenants in a group, both excluding and including the option to skip group duplicates.
Super-tenant query on multiple tenants in groups
FOR EACH Customer WHERE cust-num < 100 TENANT-WHERE
TENANT-NAME BEGINS "lowes" and TENANT-ID >= 3.
FOR EACH Customer WHERE cust-num < 100 TENANT-WHERE
TENANT-NAME BEGINS "lowes" and TENANT-ID >= 3
SKIP-GROUP-DUPLICATES.
The first FOR EACH statement in the above example behaves like the Super-tenant query for one tenant in a group example, except instead of only output for the one tenant whose tenant ID is 3, the output includes all tenants where the tenant name begins with "lowes" and the tenant ID is 3 or greater, and in any groups that might apply. In this case, because there are multiple tenants in the output, it can happen that the same group data is returned more than once. If the SKIP-GROUP-DUPLICATES option is used, as in the second FOR EACH, the TENANT-WHERE mechanism detects this and skips the output for tenants belonging to a group (or set of groups in a join) that has already been seen.
The following example executes legacy code that references tables with groups for every regular tenant in the database.
Super-tenant query using the _Tenant table involving groups
FOR EACH _Tenant WHERE _Tenant. _TenantID >= 0:
SET-EFFECTIVE-TENANT(_Tenant. _TenantID).
RUN legacy.p. /* May refer to many tables with FINDs/CREATEs/FOR EACHs/etc */
END.
The code in the above example allows a super tenant to execute the legacy code on behalf of all the tenants in the _Tenant table without reference to a specific tenant.
Because multiple tenants for the same group will produce multiple invocations of legacy.p for the group, this might cause problems. The legacy.p procedure might have many table references, with completely different group configurations for each one, in which case you would really want to have legacy.p run for each of these tenants anyway, despite the group situation.
And if all the tables in legacy.p share the same group configuration, you can then, for example, test the data for which group identity programmatically using the _Partition-Set metaschema table to avoid the repetition. For more information on testing for group identity, see Managing access to tables with groups.
