Purpose

Filter is an optional clause that specifies a filter for the primary table to limit the number of rows that are cached in the primary table. This clause is not supported for views.

Syntax

[FILTER (expression)]

where:

expression
is any valid Where clause. See "Where Clause" for details. Do not include the Where keyword in the clause. The filter for an existing cache can be removed by specifying an empty string for the filter expression, for example, FILTER().

The default value is that cached data is not filtered.

Example

The following example filters by last activity date.

FILTER (lastactivitydate >= {d'2010-01-01'})

Example A

The Referencing clause allows multiple related tables to be cached as a single entity. The following example creates a cache on the remote table account. The cache is populated with all accounts that had activity in 2010. Additionally, caches are created for the following remote tables: opportunity, contact, and opportunitylineitem. These caches are populated with the opportunities and contacts that are associated with the accounts stored in the accounts cache and the opportunity line items associated with the opportunities stored in the opportunity cache.

CREATE CACHE ON account REFERENCING (opportunity, contact, opportunitylineitem) FILTER (lastactivitydate >= {d'2010-01-01'})

Example B

The following example caches all rows of the account table with a refresh interval of 12 hours, checks whether data of the cached table needs to be refreshed on the first use, persists the data beyond the life of the connection, and stores the data in memory while the connection is active.

CREATE CACHE ON account

Example C

The following example caches all active accounts in the account table with a refresh interval of 1 day, checks whether data of the cached table needs to be refreshed when the connection is established, and discards the data when the connection is closed.

CREATE CACHE ON account REFRESH_INTERVAL 1d INITIAL_CHECK ONFIRSTCONNECT PERSIST TEMPORARY FILTER(account.active = 'Yes')