Altova MapForce 2023 Enterprise Edition

When a mapping updates a target database by means of table actions such as "Ignore If", "Update If", "Delete If", MapForce compares the source data against the target data and generates internal database update queries as a result. These internal queries are available for preview in the Output pane of MapForce, see Executing Mappings Which Modify Databases. The generated queries reflect the comparison conditions that were defined from the "Database Table Actions" dialog box.

Database Table Actions dialog box

If your source or the target data contains nullable fields, you have two options to compare null values from the source with those in the target data:

1.treat null values as equal

2.treat null values as not equal

For this purpose, the dialog box above displays a NULL equal check box next to some fields. Selecting or not selecting this check box might affect the mapping result, and is the subject of this topic. Be aware that the check box can be selected only for fields which are nullable, and when at least one table action has an "equal" or "equal (ignore case)" condition.

To avoid undesired results, you should select the NULL equal check box if all of the following conditions are true:

1.The "Database Table Actions" dialog box contains "Ignore if", "Update if", "Delete if" actions, and

2.These actions are taken against records that may contain null values, and

3.Your mapping requires that null values in the source must be treated as equal with null values in the target.

By default, the NULL equal check box is not selected. If the conditions above are true and the check box is not selected, the target database table might not be updated as expected (for example, more rows would be inserted than necessary). This happens because null values affect the data comparison. For example, in the image above, let's suppose that a record has a null email both in the source and target. If you select the NULL equal check box, that record will satisfy the Ignore If... condition and will be ignored (skipped). However, if you don't select the NULL equal check box, the record will no longer satisfy the Ignore If... condition and will be inserted into the database.

Example

To better understand null awareness in mappings, let's analyze an example where comparison of null data occurs. This example uses a Microsoft SQL Server database; however, it is also applicable for any other supported database type. Optionally, if you have Microsoft SQL Server, you can create the tables and data used in this example by running the following database script: <Documents>\Altova\MapForce2023\MapForceExamples\Tutorial\CreateNullableFields.sql.

For convenience, the database tables are illustrated below. Both tables store people data and have the same columns. Also, the column email can contain null data in both tables.

The SOURCE table

The TARGET table

Let's suppose your task is to merge data from the SOURCE table into the TARGET table. Only the new records must be inserted into the TARGET table (in this example, "Tobie Hughey"). The records which exist in both tables ("Mia Dahill" and "Fred Weinstein") must be ignored.

The task can be accomplished as follows.

1.On the Insert menu, select Database. Follow the wizard steps to connect to the database (see also Connecting to a Database). When prompted to add database objects, select the table SOURCE.

2.On the Insert menu, select Database. Connect to the database again and add the table TARGET to the mapping.

3.Draw the mapping connections between the source and target components.

4.Click the Action:Insert button and configure the database table actions as follows:

As illustrated above, a combination of "Ignore if.. Insert Rest" actions are defined. This configuration means that, for each record, the mapping checks if:

•firstname in the source is equal to firstname in the target, AND

•lastname in the source is equal to lastname in the target, AND

•email in the source is equal to email in the target.

If all the conditions above are true, the record is ignored (according to the requirement). Otherwise, a new record is inserted into the target table. The id of the new record is generated by the database, while the other fields (firstname, lastname, email) are populated with values mapped from the source.

Importantly, the check box next to email enables or disables NULL-aware comparison for this field. This check box must be selected, because email can contain null values (namely, "Mia Dahill" has a null email address). To see the role played by this check box, try updating the database two times: first time, with the check box selected, and a second time with the cleared check box.

To update the database, click the Output pane and run the menu command Output | Run SQL-Script.

If the check box is selected, MapForce has explicit indication that you want to treat the null values as equal. Therefore, the record "Mia Dahill" is not inserted in the target table, which is the intended result.

If the check box is not selected, the record "Mia Dahill" is inserted in the target table (despite that fact that it exists already), which is not the intended result. The reason is that no explicit indication was given to MapForce that you want to treat null values as equal. A similar situation would occur if you ran the following query against the database (this query retrieves no records because the null value is compared with the "=" operator so it is not NULL-aware):

In order to be NULL-aware, the query above would have to be rewritten as follows: