Hi, what happened when you set 'Empty String' to the 'Map To' parameter?
Hi, what happened when you set 'Empty String' to the 'Map To' parameter?
@Takashi, it fails also.
Does the database where the update take place allows for empty values?
Hi, what happened when you set 'Empty String' to the 'Map To' parameter?
I think it might help us understand your question a little better if you included a sample workspace. For me, it's not quite clear if your trying to map to an apostrophe (') or if the source data has an apostrophe and you want to map to Empty String. It seems like the latter, in which case @takashi suggestion to use Map To: Empty String should work, see the attached example.
nullattributevaluemapper.fmw
If you want to write a Null value to the database, you might want to set the NullAttributeMapper to If Null (or empty) then Missing. In that case no value will be written to that attribute in the table.
I think it might help us understand your question a little better if you included a sample workspace. For me, it's not quite clear if your trying to map to an apostrophe (') or if the source data has an apostrophe and you want to map to Empty String. It seems like the latter, in which case @takashi suggestion to use Map To: Empty String should work, see the attached example.
nullattributevaluemapper.fmw
@MarkAtSafe, @takashi
The data source has some <null> values that I would like to map to <empty string> and unfortunately the translation failed.
However, it works perfectly when I set the <map to> parameter of the NullAttributeMapper Transformer to <New Value> with the New Value being set to ''.
My question is why I'm having such issue and how to solve it?
P.S. A simplified version of the complete workspace is available at
https://drive.google.com/file/d/0BwjZP-sfazLMZlU5LWR5Nm11eEk/view?usp=sharing
Thanks
Does the database where the update take place allows for empty values?
@itay,
yes it does.
OK. Maybe I found the issue.
This is a simplified string expression for the SQL statement.
insert into your_table (str_field, num_field) values ('@Value(S)', @Value(N))
Here, if both the "S" and "N" attributes store the <null>, the expression generates this SQL statement.
insert into your_table (str_field, num_field) values ('', )
Naturally it causes a syntax error. The correct SQL statement should be:
insert into your_table (str_field, num_field) values ('', null)
The Oracle writer can generate a correct SQL statement internally even if an attribute contains the <null>, but you will have to build a correct statement manually when you use the SQLExecutor transformer.
Why not use the Oracle writer?
OK. Maybe I found the issue.
This is a simplified string expression for the SQL statement.
insert into your_table (str_field, num_field) values ('@Value(S)', @Value(N))
Here, if both the "S" and "N" attributes store the <null>, the expression generates this SQL statement.
insert into your_table (str_field, num_field) values ('', )
Naturally it causes a syntax error. The correct SQL statement should be:
insert into your_table (str_field, num_field) values ('', null)
The Oracle writer can generate a correct SQL statement internally even if an attribute contains the <null>, but you will have to build a correct statement manually when you use the SQLExecutor transformer.
Why not use the Oracle writer?
OK. Maybe I found the issue.
This is a simplified string expression for the SQL statement.
insert into your_table (str_field, num_field) values ('@Value(S)', @Value(N))
Here, if both the "S" and "N" attributes store the <null>, the expression generates this SQL statement.
insert into your_table (str_field, num_field) values ('', )
Naturally it causes a syntax error. The correct SQL statement should be:
insert into your_table (str_field, num_field) values ('', null)
The Oracle writer can generate a correct SQL statement internally even if an attribute contains the <null>, but you will have to build a correct statement manually when you use the SQLExecutor transformer.
Why not use the Oracle writer?
@takashi,
Using the oracle writer, I don't need anymore to use the NullAttributeTranformer and the workspace runs succesfully.
However, there is an efficiency issue. The processing is multiplied by approximately 1.5 (from 18 minutes with the New Value being of the <map to^parameter set to '' to 27 minutes with the oracle writer).
Is there anything that I can to speed up the translation?
Many thanks,
OK. Maybe I found the issue.
This is a simplified string expression for the SQL statement.
insert into your_table (str_field, num_field) values ('@Value(S)', @Value(N))
Here, if both the "S" and "N" attributes store the <null>, the expression generates this SQL statement.
insert into your_table (str_field, num_field) values ('', )
Naturally it causes a syntax error. The correct SQL statement should be:
insert into your_table (str_field, num_field) values ('', null)
The Oracle writer can generate a correct SQL statement internally even if an attribute contains the <null>, but you will have to build a correct statement manually when you use the SQLExecutor transformer.
Why not use the Oracle writer?
@arthy, If you need to build a SQL statement anyway, you can map <null>, <missing>, and <empty> for numeric type fields to constant string null using the NullAttributeValueMapper. If attributes called 'NUMERIC1' and 'NUMERIC2' should store values for nullable numeric fields in the destination database table, the parameters setting would be like this.
Then, this expression in the following SQLExecutor generates the correct SQL statement. Assume 'STRING1' and 'STRING2' are for string type fields.
insert into the_table (NUMERIC1, NUMERIC2, STRING1, STRING2)
values (@Value(NUMERIC1), @Value(NUMERIC2), '@Value(STRING1)', '@Value(STRING2)')
If all the attributes store <null> or <empty>, or those are <missing>, this statement will be generated.
insert into the_table (NUMERIC1, NUMERIC2, STRING1, STRING2)
values (null, null, '', '')
