To allow a super tenant the ability to run a single query that retrieves data for multiple tenants, OpenEdge provides the TENANT-WHERE option. When used in FOR EACH statements and query phrases, a super tenant can retrieve records for multiple tenants regardless of their effective tenancy (see Settingeffective tenancy). This is the syntax of the TENANT-WHERE option in the record phrase:

Where tenancy-expression is a relational expression, or logical combination of relational expressions, that test a valid tenant name or ID in the database that contains the record. Note that a WHERE option expression cannot involve the TENANT-ID and TENANT-NAME function or the BUFFER-TENANT-ID(buffer), BUFFER-TENANT-NAME(buffer), buffer-handle:BUFFER-TENANT-ID, and buffer-handle:BUFFER-TENANT-NAME functions and attributes if buffer or buffer-handle reference the same buffer as record; otherwise, ABL raises an error. Instead, you can use such expressions in tenancy-expression of the TENANT-WHERE option. However, the buffer functions and attributes can be used in expressions of the WHERE option if the specified buffers are not the same as record.

Note also that the TENANT-ID and TENANT-NAME functions in a TENANT-WHERE option expression must not include the optional database name parameter, because they must retrieve the tenancy of the same database connection where record is retrieved.

Although you can (and often need to) involve these functions and attributes in relational expressions of the TENANT-WHERE option, the remainder of each relational expression must involve only elements that are outside the current record buffer scope. For example, the TENANT-WHERE expressions in the following FOR EACH fragments are valid, assuming that cTenancy and iTenancy are character and integer variables, respectively:

However, the following FOR EACH fragment raises a syntax error:

The syntax error occurs because the tenancy decision has to be made before any Customer records can be accessed at all. So, there is no way to get tenant criteria out of the Customer record, such as Customer.tenid.

If you use a TENANT-WHERE option in a join, you can have only one TENANT-WHERE option at only one level of the join. In other words, the option can appear in only one record phrase of a query with multiple record phrases. This is required so that when the query is executed for each tenant in succession, the entire sub-tree of related records are from the same tenant.

Also note that use of the BUFFER-TENANT-NAME and BUFFER-TENANT-ID functions and attributes in the TENANT-WHERE option are shortcuts for the _Tenant-Name and _TenantID fields in the _Tenant metaschema table, if the buffer referenced is the same as the record buffer in the record phrase. The following two TENANT-WHERE phrases are equivalent:

In another example, you can use the SET-EFFECTIVE-TENANT function instead of using TENANT-WHERE, as in the following example:

The tenancy-expression in the TENANT-WHERE option is used to define an index scan on the _Tenant table. The tenant records selected by this scan are share locked by the AVM. If the AVM cannot get the share lock within 60 seconds, the query raises error and aborts execution. Once the record is share locked, the share lock is held until the scan of the tenant's data is completed, at which point the lock is released. The share lock is released even if the TENANT-WHERE query executes within a transaction.

Other ABL elements described in this chapter also wait on a share lock when reading the _Tenant table to obtain tenant information. For more information on the process of holding and releasing the share lock for each element, see the appropriate reference entry in OpenEdge Development: ABL Reference.