WHEN Clause — Row Filtering

The WHEN clause filters which rows from the data file are actually loaded into a table. Rows that don't match the condition are discarded (written to the discard file) rather than rejected (which is for format errors).

Syntax

INTO TABLE hr.employees
WHEN condition

The condition compares a field or a fixed byte position against a literal string or the keyword BLANKS.

Operators

Operator	Meaning
=	Equal to string literal
!= or <>	Not equal
AND	Both conditions must be true
OR	Either condition
NOT	Negate

Comparisons are string comparisons — there is no numeric or date comparison in WHEN. Pad short literals with spaces if needed to match field widths.

Filtering on a field value

LOAD DATA
INFILE 'employees.csv'
APPEND

INTO TABLE hr.active_employees
WHEN status = 'A'
FIELDS TERMINATED BY ','
( employee_id, first_name, last_name, status FILLER, hire_date DATE "YYYY-MM-DD" )

INTO TABLE hr.inactive_employees
WHEN status = 'I'
FIELDS TERMINATED BY ','
( employee_id, first_name, last_name, status FILLER, hire_date DATE "YYYY-MM-DD" )

Rows with status = 'A' go to active_employees; rows with status = 'I' go to inactive_employees. Any other status value is discarded.

Filtering on a fixed byte position

When the record type indicator is at a known byte offset (common in fixed-width files):

LOAD DATA
INFILE 'transactions.dat' "FIX 100"
APPEND

INTO TABLE finance.sales_txn
WHEN (1:1) = 'S'
( txn_type POSITION(1:1) FILLER,
  txn_id   POSITION(2:9)  INTEGER EXTERNAL,
  amount   POSITION(10:20) DECIMAL EXTERNAL,
  txn_date POSITION(21:30) DATE "YYYY-MM-DD" )

INTO TABLE finance.refund_txn
WHEN (1:1) = 'R'
( txn_type POSITION(1:1) FILLER,
  txn_id   POSITION(2:9)  INTEGER EXTERNAL,
  amount   POSITION(10:20) DECIMAL EXTERNAL,
  txn_date POSITION(21:30) DATE "YYYY-MM-DD" )

(1:1) = 'S' checks the raw byte at position 1 of each record.

AND / OR compound conditions

INTO TABLE hr.us_managers
WHEN country = 'US' AND job_code = 'MGR'
FIELDS TERMINATED BY ','
( employee_id, last_name, job_code FILLER, country FILLER, salary DECIMAL EXTERNAL )

INTO TABLE hr.special_roles
WHEN job_code = 'VP' OR job_code = 'CXO'
FIELDS TERMINATED BY ','
( employee_id, last_name, job_code FILLER )

WHEN with BLANKS

Filter rows where a field is all spaces (effectively NULL in the source):

INTO TABLE hr.employees_no_dept
WHEN department_id = BLANKS
FIELDS TERMINATED BY ','
( employee_id, last_name, department_id FILLER )

NOT condition

INTO TABLE hr.non_temp_employees
WHEN job_code != 'TMP'
FIELDS TERMINATED BY ','
( employee_id, last_name, job_code FILLER )

What happens to filtered rows

• Rows that don't match any INTO TABLE block's WHEN condition → written to the discard file (.dsc)
• Rows that match but fail format conversion → written to the bad file (.bad)
• The log file reports: "Rows successfully loaded", "Rows not loaded due to data errors", "Rows not loaded because all WHEN clauses were false"

WHEN vs NULLIF

Use case	Solution
Skip entire row	WHEN condition on INTO TABLE
Set a column to NULL	NULLIF col = value on the column

These are different operations. WHEN filters whole rows; NULLIF affects a single column value.

Example — multi-table load from a single feed file

-- bank_feed.ctl
OPTIONS ( ERRORS=50 )
LOAD DATA
INFILE 'bank_feed.dat'
APPEND

INTO TABLE bank.deposits
WHEN (1:3) = 'DEP'
( rec_type   POSITION(1:3)   FILLER,
  account_id POSITION(4:15)  INTEGER EXTERNAL,
  amount     POSITION(16:28) DECIMAL EXTERNAL,
  txn_date   POSITION(29:38) DATE "YYYY-MM-DD",
  reference  POSITION(39:70) CHAR )

INTO TABLE bank.withdrawals
WHEN (1:3) = 'WDR'
( rec_type   POSITION(1:3)   FILLER,
  account_id POSITION(4:15)  INTEGER EXTERNAL,
  amount     POSITION(16:28) DECIMAL EXTERNAL,
  txn_date   POSITION(29:38) DATE "YYYY-MM-DD",
  reference  POSITION(39:70) CHAR )

INTO TABLE bank.transfers
WHEN (1:3) = 'TRF'
( rec_type    POSITION(1:3)   FILLER,
  from_acct   POSITION(4:15)  INTEGER EXTERNAL,
  to_acct     POSITION(16:27) INTEGER EXTERNAL,
  amount      POSITION(28:40) DECIMAL EXTERNAL,
  txn_date    POSITION(41:50) DATE "YYYY-MM-DD" )

Best practices

• Always use WHEN on every INTO TABLE in a multi-table load — a row without a matching WHEN goes to the discard file, not to all tables
• Keep the condition simple: string equality on a short type code is fast and predictable
• Use POSITION(n:m) form (not field names) for byte-position checks in fixed-width files — it avoids ambiguity with the field list
• Check the discard file count in the log; unexpectedly high discards often mean the WHEN condition doesn't match the actual data format
• In multi-table loads, if the same field is used in the WHEN condition and also loaded into a column, mark the column as FILLER to avoid loading the type indicator itself