You can use conditional 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 conditional 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 conditional operation expressions. The following table shows valid values of a conditional expression.
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 conditional 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 conditional 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 conditional 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;