Aggregator stage warning message : Implicit conversion

Chandrathdsx · Post by **Chandrathdsx** » Tue May 19, 2009 9:54 am

Job design:
dataset 1
dataset 2 --> Funnel --> Aggregator --> Dataset3.
I am doing a sum on source field "Sales_amt" decimal(38,10) not null to an output field "Sales_amt" of decimal(38,10) not null using Agregator stage. the output finally gets loaded into Oracle Target with datatype "Number" [no precision]. I am getting following warning messages in Aggregator:

1.When checking operator: When binding output interface field "Sales_amt" to field "Sales_amt": Implicit conversion from source type "dfloat" to result type "decimal[38,10]": Possible range/precision limitation.

2.When checking operator: When binding output interface field "Sales_amt" to field "Sales_amt": Converting a nullable source to a non-nullable result;
a fatal runtime error could occur; use the modify operator to
specify a value to which the null should be converted.

Some of the trouble shoot I did was, for warning #2, changing the input and output field to null from not null in Aggregator stage to get rid of message two, even though my both input and outputs are not null.
for warning #1, change the output to 'double' in aggegator stage. But, while loading into Oracle table with data type as "number" will still need to convert the double back to decimal[38,10].

Could some one pls help with this to get rid of these messages?..

Thank you.

priyadarshikunal · Post by **priyadarshikunal** » Tue May 19, 2009 10:09 am

Use the property Default to decimal and define the valoue as 38,10.

If you are using max/min function then you can use preserve data type option to preserve the datatype on input.

Chandrathdsx · Post by **Chandrathdsx** » Tue May 19, 2009 2:30 pm

thank you priyadarshikunal. It resolved the issue with the implecit conversion of data type. But, I am still getting the warning # 2:
2.When checking operator: When binding output interface field "Sales_amt" to field "Sales_amt": Converting a nullable source to a non-nullable result;
a fatal runtime error could occur; use the modify operator to
specify a value to which the null should be converted.
Any help pls..
thanks again..

priyadarshikunal wrote:Use the property Default to decimal and define the valoue as 38,10.

If you are using max/min function then you can use preserve data type option to preserve the datatype on input.

chulett · Post by **chulett** » Tue May 19, 2009 3:08 pm

Mark the output field of your sum as Nullable: Yes.

priyadarshikunal · Post by **priyadarshikunal** » Wed May 20, 2009 9:27 am

Please mark this post as resolved if it is.

Chandrathdsx · Post by **Chandrathdsx** » Thu May 21, 2009 1:03 pm

chulett wrote:Mark the output field of your sum as Nullable: Yes. ...

thank you for all you help.

I had been doing by marking the field as nullable in the output. But it is not null in the input as well it will be loaded into Oracle table that too not null. As a work around I am marking the output in aggregator as nullable and then I am converting this to not null in the next stage to be able to load the TGt Oracle table. Any other ideas to cut down extra not null to null and then null to not null conversion is highly appreciated.

Thank you!

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Thu May 21, 2009 8:27 pm

It is a limitation of using the aggregator - even though you know the results will never be null, there is a theoretical possibility of a null value, so the designers set it so the output MUST be nullable.

If you are 100% sure of no null values, then just put a transformer after the aggregator that switches the output field from null to not null on the output column.