In this blog I would be talking about how to handle NULL values in Oracle Data Integrator. Now, the below snapshot shows the mappings for moving a flat file data to an Oracle database table.
The below snapshot shows the source file that is going to be loaded into the system.
Observe that the first two lines in the flat file which represent the Entity field is NULL.
A snapshot of the target Oracle table called FLAT_FILE_TARGET is as shown below.
I now run the mapping and it is executed successfully as shown in the below couple of snapshots.
The target system after updating the data is as shown in the below snapshot.
Observe that in the first two records the Entity field is mapped as NULL.
Now, in many cases, we would want to set a placeholder value rather than NULL going to the target system. In order to do this, we can define a SQL expression for the entity field to ensure that the mapping pushes some standard placeholder value whenever it encounters NULL values in the Entity field.
In order to define a SQL mapping, I add an SQL expression as shown in the below couple of snapshots.
The SQL mapping is a simple CASE statement that says when source Entity value is NULL, I should output “No_Entity” else I just move the value of the entity as is. Observe that this is because ODI uses ELT with a relational system at the backend for the grunt work that we can use these SQL statements.
Now the below snapshot shows the mapping being executed.
Observe that the mapping has executed successfully as shown in the above snapshot.
The contents of the FLAT_FILE_TARGET table is as shown in the below snapshot. Observe that the first two rows are updated to be No_Entity by the ODI mapping.
A snapshot of the target Oracle table called FLAT_FILE_TARGET is as shown below.
Now, in many cases, we would want to set a placeholder value rather than NULL going to the target system. In order to do this, we can define a SQL expression for the entity field to ensure that the mapping pushes some standard placeholder value whenever it encounters NULL values in the Entity field.
In order to define a SQL mapping, I add an SQL expression as shown in the below couple of snapshots.
The SQL mapping is a simple CASE statement that says when source Entity value is NULL, I should output “No_Entity” else I just move the value of the entity as is. Observe that this is because ODI uses ELT with a relational system at the backend for the grunt work that we can use these SQL statements.
Now the below snapshot shows the mapping being executed.
The contents of the FLAT_FILE_TARGET table is as shown in the below snapshot. Observe that the first two rows are updated to be No_Entity by the ODI mapping.
hi Sibin,
ReplyDeleteif in case i have to omit the whole record for the case where entity is null, is it doable?
Thanks,
Runita