“nullto” Method - Connect_CDC - connect_cdc_mimix_share - Latest

The following examples show how you could use the "nullto" method:

• Scenario 1

You wish to map a source column to a target:

• source column nullable ZIP_CODE CHAR(5)

• target ZIP_CODE CHAR(10) NOT NULL,

Sample expression

nulltostring(ZIP_CODE,'00000');

This says:

• If ZIP_CODE is null, then send 00000 to the target.

• Otherwise, send whatever is in ZIP_CODE.

This expression ensures that you can replace NULL with some default value and always have a target value. You should avoid NULL as this will cause an error at the target.

• Scenario 2

You wish to combine 2 columns and send them to the target together:

• source column ZIP_CODE is NOT NULL

• source column ZIP_SUFFIX is nullable and can contain NULL

At the target you want a zip code of the form zzzzz-nnnn.

Sample expression:

concat(ZIP_CODE, '-', nulltostring(ZIP_SUFFIX, '0000'));

This says:

• Concatenate the suffix to the zip code with a dash between them

• If the suffix is null, use 0000 instead null.

• Scenario 3

You wish to combine 2 columns and send them to the target together:

• source column ZIP_CODE can contain NULL

• source column ZIP_SUFFIX is nullable and can contain NULL

At the target you want a zip code of the form zzzzz-nnnn.

Sample expression:

concat( nulltostring(ZIP_CODE, '00000'), '-'. nulltostring(ZIP_SUFFIX, '0000') );

This says:

• Concatenate the suffix to the zip code with a dash between them.

• If either the zip code or suffix is null, use 0000 instead null.

If both zip and suffix are null, then 00000-0000 is sent to the target.

• Scenario 4

There is always a zip (it is NOT NULL) and the suffix is nullable, but, in the case where the suffix is NULL, you only want to send the zip:

begin returns char;
   if (ZIP_SUFFIX is null)
       return ZIP_CODE;
   else
        return concat(ZIP_CODE, '-', ZIP_SUFFIX);
end;

This says:

• If the suffix is null, send only the zip.

• If both a zip code and a suffix exist, concatenate the suffix to the zip code with a dash between them.

• Scenario 5

If the zip and the suffix are nullable, but, in the case where the suffix is NULL, you only want to send the zip:

begin returns char;
   if (ZIP_SUFFIX is null)
        return nulltostring(ZIP_CODE, '00000');
   else
        return concat( nulltostring(ZIP_CODE, '00000'), '-', ZIP_SUFFIX);
end;

This says:

• If the suffix is null, send only the zip.

• If the zip is null, send ’00000’.

• If both a zip code and a suffix exist, concatenate the suffix to the zip code with a dash between them.

For an unqualified "column name", the Expression Handler assumes that it is in the source table. If the column name is qualified, then the Expression Handler assumes that the user is fully qualifying it so the complete name must be specified, for example:

database(if applicable).schema name.table name.column name

For a name identifier (as opposed to a method identifier), the EH first checks to see if is a variable - variables cannot be qualified. Then if it is unqualified it looks in the default source table. If it is qualified (a name with a dot in it), then it assumes it is a fully qualified column name.