You can use case expressions to define a control file. This enables you to accomplish complex data processing tasks by performing simple logical and arithmetic operations.
Syntax
The following example shows the syntax of a case expression:
--Simple Case expression
CASE <expr> WHEN <constant> THEN [constant | epxr] ELSE [constant | expr] END;
-- Search Case expression
CASE WHEN <condition> THEN [constant | expr] ELSE [constant | expr] END;
The parameter <expr> indicates case operation expressions. The following table shows valid values.
| Expression | Return type | Description |
|---|---|---|
| is [not] null | Boolean | Verifies whether the value is null. Example: case when col1 is null then 'Y' else 'N' end;. |
| not <condition> | Boolean | Takes the inverse of the true value of the case expression. Example: case when not col1 is null then 'N' else 'Y' end;. |
| [not] in (string_list) | Boolean | Verifies whether values in a column exist in the specified list. Example: case when col1 in ('1','3') then 'Y' else 'N' end;. |
Logical operation
You can use case expressions to perform simple logical operations. Priority customization is not supported. The default priority of operations is determined by the sequence of expressions.
Logical operations include the AND/&& and OR/|| operations. Example:
CASE
WHEN LENGTH(TRIM(C1)) = 17 AND SUBSTR(TRIM(C1, 13, 2)) < 60
THEN TO_TIMESTAMP(TRIM(TRIM(C1), 'yyyyMMddHHmmssSSS'))
WHEN LENGTH(TRIM(C1)) = 17
THEN SYSTIMESTAMP
ELSE TO_TIMESTAMP('00010101000000000', 'yyyyMMddHHmmssSSS')
END;
Arithmetic operation
You can use case expressions to perform simple arithmetic operations. Priority customization is not supported. The default priority of operations is determined by the sequence of expressions.
Arithmetic operations include addition (+), subtraction (-), multiplication (*), division (/), and modulus operation (MOD). Example:
CASE
WHEN TRIM(C1)+1>2 THEN '>2'
WHEN TRIM(C1)+1=2 THEN '=2'
ELSE '<2'
END;