Examples

Click on this push button to display a list of sample predicates.

Sample WHERE clauses:

The following examples show WHERE clauses (row predicates) that you can use to filter rows of the target table. Most are very general and designed for you to use as a model. Some examples may not conform to your database manager's SQL syntax. See your database's SQL reference for more information about WHERE clauses.

• WHERE clause specifying rows with specific values

  To copy only the rows that contain a specific value, such as MGR for employees that are managers, use a WHERE clause like:

  EMPLOYEE = 'MGR'
  

• WHERE clause specifying rows with a range of values

  To copy only the rows within a range, such as employee numbers between 5000 and 7000, use a WHERE clause like:

  EMPID BETWEEN 5000 AND 7000
  

• WHERE clause to schedule copy requests

  To specify that a table be copied between a designated time period, such as 5:00 PM and 8:00 PM, use a WHERE clause like:

  HOUR(CURRENT TIMESTAMP) > 17 AND HOUR(CURRENT TIMESTAMP) < 20
  

• WHERE clause to filter change aggregate tables

  In a change aggregate table created by an SQL expression, such as SUM(CUSTBALANCE), you can use a WHERE clause to specify which rows are used in the calculation to create the table.

  For example, if you want to include only recent inserts and updates, and filter out deleted rows, use a WHERE clause like:

  IBMSNAP_OPERATION IN('I','U')
  

  Notes for this example:

  • IBMSNAP_OPERATION is a column in a change data table that records the type of change being applied to a source table.
  • A change aggregate table requires a change data table.
  • If the change aggregate table contains before-image columns, you must include the before-image columns in the GROUP BY clause, even though these columns are not displayed in the Available columns list.

You can use the following operators in an SQL expression for a WHERE clause or a computed column. All normal restrictions apply to the operators. See your database's SQL reference guide for information about additional operators and restrictions for your database.

Operators:

CONCAT

||

/

*

+

-

( )

Column functions:

AVG

COUNT

MAX

MIN

SUM

Scalar functions:

CHAR

DATE, DAY, DAYS

DECIMAL

FLOAT

HOUR

INTEGER

LENGTH

MICROSECOND

MINUTE

MONTH

SECOND

SUBSTR

TIME

TIMESTAMP

TRANSLATE

VALUE

VARGRAPHIC

YEAR

Predicate comparison operators:

=

<>

<

>

<=

>=