Use of Lookups in Conversion Mapping
By StuRamage on Sep 02, 2007
Question Received: When we develop the data migration the data conversions will require lookup tables to allow translation of data from the legacy system to CC&B. Where would we specify the lookup tables? We think it may be in the Input Table Definition. Can the Constant Value or Predicate columns be used to specify a SQL statement or script that can translate a value via a lookup table? Does the lookup table have to be declared to the Mapper or does it just have to be in the Oracle database?Answer: The Lookups and all other 'temporary' tables used as part of the conversion effort can be defined at either the input or staging table level, however if you are building them at the staging table level then you should use the standard CC&B ruling of prefixing the tables with 'CM_' . The Constant value can definitely be used to build SQL or PL/SQL components to be used for the mapping and these tables do not need to be defined within the mapper unless you are also planning on using the fields to perform simple mapping (much like you are able to do with the input tables).