TENANT-WHERE option of the record phrase

Syntax

{ record [ field-list ] } [ OF table ] [ WHERE expression ]
[ TENANT-WHERE tenancy-expression [ SKIP-GROUP-DUPLICATES ] ]
[ USE-INDEX index | TABLE-SCAN ]
[ other-existing-syntax ]

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:

FOR EACH Customer TENANT-WHERE BUFFER-TENANT-NAME(Customer) = cTenancy ...

FOR EACH Customer TENANT-WHERE BUFFER-TENANT-ID(Customer) > iTenancy ...

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

FOR EACH Customer TENANT-WHERE BUFFER-TENANT-ID(Customer) > Customer.tenid. ...

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:

FOR EACH Customer TENANT-WHERE BUFFER-TENANT-NAME(Customer) BEGINS "E":
FOR EACH Customer TENANT-WHERE _Tenant._Tenant-Name BEGINS "E":

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

FOR EACH _Tenant WHERE _Tenant._Tenant-Name BEGINS "E":
  SET-EFFECTIVE-TENANT(_Tenant._Tenant-Name).
  FOR EACH Customer:
. . .