WHERE

Syntax

Data Mover Command {record | *} WHEREcondition(s)[;var#1_type,_var#1_value,var#2_type, var#2_value,...var#n_type,var#n_value];

Note:

In an EXPORT statement, the WHERE modifier must be on the same line as the EXPORT command.

Description

Retrieves a partial set of rows from the data source. The data source could be a data file or the database. The syntax and conditions of a Data Mover WHERE clause are similar to a WHERE clause in SQL. For EXPORT, you can write the WHERE clause with comparison operands in-line or as bind variables. For IMPORT the WHERE clause only supports comparison operators as bind variables. Parentheses are not support in IMPORT. You can also use nested SELECT statements.

WARNING:

When comparing string or character values, use only US-ASCII (seven-bit ASCII) values. Characters beyond this range can produce errors in the export file.

Parameters

EXPORT

IMPORT

Example

Here's an example of a WHERE clause using both an inline operand and bind variables in an EXPORT script:

EXPORT JOB WHERE
  EFFDT > :1 AND 
  HOURLY_RT > :2 
  AND GRADE = 'ADV';DATE,1994-01-01,NUMBER,100;

There are no single or double quotation marks around the bind data, as they are not necessary, and dates are formatted as YYYY-MM-DD. The valid data types for binding are CHAR, NUMBER, DATE, TIME, DATETIME, LONG, and IMAGE. Not all database platforms support LONG or IMAGE data types in the WHERE clause, so you should not use WHERE clauses with these data types.

The following operators are supported in an import WHERE clause: =, < >, <, >,< =, > =, and simple uses of AND and OR. For example, in the following formula, if A, B, and C are true, or if D is true, or if E is true, then the whole statement is true

WHERE 
  A = :1 AND B = :2 AND C = :3 
  OR D = :4 
  OR E = :5;NUMBER,10,NUMBER,20,NUMBER,30,NUMBER,0,NUMBER,1;