/FILTER - Connect_ETL - 9.13

Connect ETL Data Transformation Language (DTL) Guide
Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect (ETL, Sort, AppMod, Big Data)
Version
9.13
Language
English
Product name
Connect ETL
Title
Connect ETL Data Transformation Language (DTL) Guide
Copyright
2023
First publish date
2003
Last updated
2023-09-11
Published on
2023-09-11T19:01:45.019000

Purpose

To retain only records that satisfy certain conditions for further processing.

Format

/FILTER criteria [criteria...]
where
criteria =

{DISCARDSHORT min_length }

{DISCARDFIRST num_records}

{DISCARDBLANK }

{DISCARDAFTER num_records}

{RETAINIF condition }

Arguments

condition The name of a condition defined in a /CONDITION option or a level 88 in a COBOL copybook supplied through a /datadictionaryoption.
min_length The minimum record length; records shorter than that length are discarded.
num_records The consecutive number of records to discard or to keep.

Location

A /FILTER option can appear anywhere in the task definition; however, its position in relation to the first target option as well as any subsequent target options is important:

  • For records filtered before the main task transformation, the /FILTER option must be listed before the first target option. (These source filters are applied before the main task transformation.)
  • For records filtered for a specific target, the /FILTER option must be listed after the corresponding target option. When multiple targets exist, the /FILTER option applicable to a specific target must be listed after the option defining the target and before the option defining the next target, if any.

Notes

Source Record Filter

A source record filter is applied before records are released to the main task transformation: copy, sort, merge, or aggregate.
Note: Source record filter is not supported for join tasks

Only one source record filter can be specified per task.

Source Type and Source Filter Option Arguments

The option arguments that can be specified within a source filter are dependent upon the type of source.

The following /FILTER option arguments can be specified for file, buffer, and procedure sources only:

  • DISCARDSHORT
  • DISCARDFIRST
  • DISCARDBLANK
  • DISCARDAFTER

The RETAINIF option argument can be specifed for all sources.

Source Filter Option Arguments

Regardless of the order in which the option arguments are specified, they are applied in the following order:
  1. DISCARDSHORT - All records shorter than the specified minimum length are discarded.
  2. DISCARDFIRST - The specified number of records are discarded from the input stream starting from the first non-short record in the first input file. If the first file does not have enough records, records are discarded from the second file and subsequent files until the specified number of records are discarded.
  3. DISCARDBLANK - All blank records are discarded. A record is considered to be blank if either the record length is 0 or all bytes in the record are space characters.
  4. DISCARDAFTER - Only the specified number of records are collected for further processing. The RETAINIF sub-option, if specified, may further reduce the number of records.
  5. RETAINIF - Only records that satisfy the specified condition are retained for further processing.

Target Record Filters

A target record filter applicable to a specific target is applied after records are released from the main task transformation: copy, sort, merge, aggregate, or join.
Note: Target record filter is supported for all transformations including join tasks.

Only one target record filter can be specified per target.

Target Filter Option Argument

Only the RETAINIF sub-option can be specified in the target filters.

Null condition

When the condition evaluates to NULL, it is equivalent to FALSE. The record is discarded.

Examples

/FILTER retainif in_usa
This option specifies that the records to be retained are those that meet the condition in_usa.
/FILTER retainif in_usa discardblank
This option specifies that the records to be retained are those that meet the condition in_usa and that blank records are discarded.
/FILTER discardshort 3 discardfirst 10 discardafter 100

This option specifies that the records having recordlength less than 3 are discarded. Next, the first ten non-short records are discarded, and 100 records are considered for processing.