Super-tenant query for no explicit tenant

FOR EACH Customer WHERE cust-num < 100.

If the code in the above example is executed by a regular tenant with no Customer tenant group, the user sees only their own customers with cust-num < 100.

If the code in the above example is executed by a default tenant or a super tenant that has not executed SET-EFFECTIVE-TENANT, the user sees only the default tenant's customers with cust-num < 100. If there is no default partition, the user will see no data.

If the code in the above example is executed by a super tenant that has used SET-EFFECTIVE-TENANT to acquire an effective tenancy, the user sees only that tenant's customers with cust-num < 100.

The following example adds a simple TENANT-WHERE option that selects one tenant for the simple query.

Super-tenant query for one tenant

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. The super tenant sees the same result regardless of their effective tenancy.

The following example expands the TENANT-WHERE option to select multiple tenants based on compound conditions.

Super-tenant query for multiple tenants qualified by name and ID

FOR EACH Customer WHERE cust-num < 100 
  TENANT-WHERE TENANT-NAME BEGINS "lowes" AND TENANT-ID >= 3.

The code in the above example behaves like the Super-tenant query for one tenant 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.

The following example executes legacy code for every regular tenant in the database.

Super-tenant query using the _Tenant table

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.

The following example reads separate buffers, each with its own effective tenancy.

Super-tenant queries on buffers with various effective tenancies

DEFINE BUFFER bufCust FOR Customer.
 SET-EFFECTIVE-TENANT(1).
 
FIND FIRST Customer.
SET-EFFECTIVE-TENANT(3).
FIND FIRST bufCust.

The code in the above example shows how a super tenant might read data simultaneously from various tenants by defining different buffers for the same multi-tenant table and reading each one with a different effective tenancy. The code in this figure reads the first Customer for tenant 1 into the Customer buffer, and the first Customer for tenant 3 into the bufCust buffer. If the super tenant later becomes the effective default, or any other regular tenant, they can continue to update the previously populated buffers for their corresponding tenants (assuming appropriate can-* permissions). For more information on multi-tenant updates, see Super-tenant data updates.