Mapping Design and Core Transformations
Mapping design is the largest scoring area: you must know when to reach for each core transformation, how ports and expressions evaluate, and how active transformations alter the row stream.
Ports and evaluation
Every transformation has input, output, both and variable ports. In an Expression transformation, variable ports evaluate in order before output ports on every row. Useful built-ins: IIF, DECODE, TO_DATE, ISNULL, SYSDATE, ERROR, ABORT, and NEXTVAL/CURRVAL for a Sequence Generator. Active transformations change row count (Filter, Router, Aggregator, Rank, Normalizer, Update Strategy); passive ones do not (Expression, Lookup, Joiner, Sequence Generator).
Core transformations
- Filter: one condition; rows evaluating false are dropped.
- Router: ordered groups, each with a condition; a row goes to the first matching group and the default group catches the rest, so write mutually exclusive conditions.
- Aggregator: group-by ports plus aggregate expressions produce one row per group at the group break; enable Sorted Input when data arrives ordered.
- Joiner: master and detail, equi-join conditions only, with Normal, Master Outer, Detail Outer and Full Outer types.
- Union: merges inputs by port position, not name; the first input supplies port names.
- Sequence Generator: NEXTVAL returns the next value, CURRVAL repeats it.
- Update Strategy: DD_INSERT, DD_UPDATE, DD_DELETE, DD_REJECT, honoured only when the session treats source rows as Data Driven.
Worked example
To route orders, create Router group HIGH with AMOUNT > 1000, group LOW with AMOUNT <= 1000, and an empty default group. A row of 1500 matches HIGH only, because groups are evaluated top to bottom and evaluation stops at the first match.
Traps
- Sorted Input on an Aggregator when the data is not sorted: wrong group breaks and no error.
- Non-equality or OR conditions in a Joiner, which are rejected.
- Calling NEXTVAL twice in one row, consuming two sequence values.