Purpose

Specifies a filter for the primary table to limit the number of rows that are cached in the primary table; it is optional. 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().

Default

The default behavior is that cached data is not filtered.

Example A

Referencing clause allows multiple related tables to be cached as a single entity. This example creates a cache on the remote table account. The cache is populated with all accounts that have 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

This 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

This 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')