Skip to main content
Question

Mapping null attributes

arthy
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Contributor

I'm doing a lot of point on area overlay and area on area overlays. The values are all coming from an oracle database.

When using the nullattributemapper as shown below

it works.

However, the problem here is that I'm updating another oracle table and this option provides some attribute values to be only an apostrophe (') while I want to have those attribute values being mapped to an empty string.

So instead of choosing map to 'New value', I have tried without success to map those values to 'empty string' and I don't understand what is the issue.

11 replies

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • December 25, 2016
Hi, what happened when you set 'Empty String' to the 'Map To' parameter?

 

 

arthy
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Author
  • Contributor
  • December 25, 2016
takashi wrote:
Hi, what happened when you set 'Empty String' to the 'Map To' parameter?

 

 

@Takashi, it fails also.

 

 

itay
Supporter
Forum|alt.badge.img+16
  • itaySupporter
  • Supporter
  • December 25, 2016
Does the database where the update take place allows for empty values?

 

 

M
Forum|alt.badge.img+2
  • markatsafe
  • December 27, 2016
takashi wrote:
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

 

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • December 28, 2016

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.

arthy
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Author
  • Contributor
  • December 29, 2016
markatsafe wrote:
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

 

 

arthy
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Author
  • Contributor
  • December 29, 2016
itay wrote:
Does the database where the update take place allows for empty values?

 

 

@itay,

 

yes it does.

 

 

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • December 29, 2016
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
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • December 29, 2016
arthy wrote:
@Takashi, it fails also.

 

 

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
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Author
  • Contributor
  • December 29, 2016
takashi wrote:
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,

 

 

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • December 30, 2016
takashi wrote:
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.

 

0684Q00000ArMajQAF.png

 

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, '', '') 

Reply


Helpful Members This Week

Community Stats

30,947
Posts
117,282
Replies
38,743
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.