WhereScape RED enables you to create Custom Data Type Mapping Sets. This gives you the ability to automatically change the data type of any column or to add column transformations, when dragging and dropping new Load tables.
The examples in this topic demonstrate how Custom Data Type Mapping Sets can be configured, using the following variables:

  • %length
  • %scale
  • %precision
  • %table_name
  • %column_name
  • %format
  • %prompt

%length

In the example below, when converting varchar in a standard file to SQL Server format, we follow the following steps in the given order:

  • If the varchar is of a length less than or equal to 63, the data type will become varchar(64).
  • If the first step was NOT applied, i.e. the varchar is of a length greater than 63, then the data type will become varchar(%length); where we substitute the length for the variable '%length'. Thus if the varchar is of length 64 then the resulting data type will be varchar(64), but if the varchar is of length 123 then the resulting data type will be varchar(123).

%scale

In the example below, when converting a decimal in SQL Server to SQL Server format,  the following steps are done in the given order:

  • If the decimal has a scale of zero, the data type will become numeric(%precision); where we substitute the number of digits in the number for the variable '%precision'. Thus if the decimal has 8 digits then the resulting data type will be numeric(8).
  • If the first step was NOT applied, i.e. the decimal has a scale of 1 or greater, then the data type will become numeric(%precision,%scale); where we not only substitute the number of digits in the number for the variable '%precision', but we also substitute the scale for the variable '%scale'. Thus, if the decimal is made up of 8 digits and has 3 digits after the decimal point (example 12345,678), the resulting data type will be numeric(8,3).

The Scale> is the number of digits to the right of the decimal point in a number.

%table_name and %column_name

In the example below, the following transformations are used to handle NULL for different lengths of varchars:

  • If the varchar is 1 or 2 digits/chars long, the data type will become varchar(%precision); where we substitute the number of digits/chars in the varchar for the variable '%precision'. Secondly, the value of the column will become the column value (if it is not null), else it will become 'U'.
  • If the varchar is 3-6 digits/chars long, the data type will become varchar(%precision); where we substitute the number of digits/chars in the varchar for the variable '%precision'. Secondly, the value of the column will become the column value (if it is not null), else it will become 'UNK'.
  • If the varchar is 7 or more digits/chars long, the data type will become varchar(%precision); where we substitute the number of digits/chars in the varchar for the variable '%precision'. Secondly, the value of the column will become the column value (if it is not null), else it will become 'UNKNOWN'.

%format

In the example below, the following transformations are used to convert a certain character field to a date:

  • If the varchar has a length of 1-10, the data type will become the date and the value of the column will become the date 20131212 (a chosen date in the future).
  • If the varchar has a length of 11, the data type will become date and the value of the column will use the transformation TO_DATE(%table_name.%column_name,%format); where we substitute 'YYYYMMDD' for the variable '%format'. Thus the value of the column will be converted to a date of format 'YYYYMMDD'.
  • If the varchar has a length of 12 or greater, the data type will become the date and the value of the column will become the date 20181212 (a chosen date in the future).

%prompt

In the example below, %prompt is used to help the user define a mapping for an unknown data type that is not already mapped in the previous mapping rules.This variable must be used with a custom Data Type mapping set, as described in the following steps:

  • Create a new custom set or copy from an existing set.
  • Create a new Data Type mapping with a From Data Type of star ({}) and a *To Data Type of %prompt.  Click OK to save the New Data Type Mapping to the Custom set.

  • As the table is dragged and dropped to the middle pane, RED prompts to have the new data type mapping defined.
  • No labels