Writing Rules

A rule is a simple boolean relationship between a column of the table being loaded or a field from the input record and a value. A rule can be an INCLUDERULE, meaning the relationship must be satisfied for the record to be loaded or an EXCLUDERULE meaning the record will not be loaded if the condition is satisfied.

You write rules directly in the configuration file.

Syntax

───┬──── <parameter name> ───┬──┬──── IS ──┬─────────┬── NULL ─────┬─────┤
   └───[──<field number>──]──┘  │          └── NOT ──┘             │
                                │                                  │
                                │                                  │
                                └─┬── <  ──┬─── <field value> ─────┘
                                  ├── <= ──┤
                                  ├── =  ──┤
                                  ├── != ──┤
                                  ├── <> ──┤
                                  ├── >= ──┤
                                  └── >  ──┘

<parameter name>

The parameter name must be a literal value that is the name of one of the table columns being loaded.

<field number>

The field number must be a numeric literal that is the ordinal position of one of the fields in the input record. Fields are numbered starting from 1. The field identified must be one that is associated with one or more of the table columns being loaded. If the field is associated with more than one table column, those table columns must all be of the same type.

<field value>

──┬─── <character field value> ───┬──┤
  ├─── <numeric field value> ─────┤
  ├─── <date field value>  ───────┤
  ├─── <time field value> ────────┤
  └─── <datetime field value> ────┘

<character field value>

The character field value is a string literal enclosed in double quotes. Double quotes can be embedded within the literal.

<numeric field value>

The numeric field value is a numeric literal.

<date field value>

The date field value is a date expressed as YYYY-MM-DD.

<time field value>

The time field value is a time expressed as hh:mm:ss[.fff]

<datetime field value>

The datetime field value is a datetime expressed as YYYY-MM-DD [hh:mm:ss[.fff]]

Examples

  • The following example causes all records from the data file in which the field containing the HIRE_DATE has a value before January 1, 2013 to be excluded from the load: 

    EXCLUDERULE = HIRE_DATE < 2013-01-01

  • The following example causes all records from the data file in which the field containing the FIRST_NAME is null to be excluded from the load: 

    EXCLUDERULE = FIRST_NAME IS NULL

  • The following example causes only records from the data file in which the field containing the COUNTRY_CODE with a value of 64 to be loaded: 

    INCLUDERULE = COUNTRY_CODE = 64

  • The following example causes only records from the data file in which the sixth field is greater than 100 to be loaded. Note that the sixth field must be associated with one or more numeric columns being loaded. 

    INCLUDERULE = [6] > 100
Prev  Up  Next
Rules and Filters  Home  Writing Filters