decimal_from_string warning for same data length

[adityavarma]

I am converting a Varchar to Decimal and it is throwing the below error:

Conversion error calling conversion routine decimal_from_string data may have been lost

Source Datatype: Varchar 10
Target Datatype: Decimal (10,0)

I am using an existing Dataset (In Production) which has a datatype of Varchar(10) as source and writing into Netezza database.
I tried increasing the length of the Varchar fields in a transformer to Varchar 12 and then write into the Decimal(10,0) but i still get the same warnings

I want to get rid of the warnings without using the Message handler
can you please suggest ?




Thanks,
Aditya

[Mike]

I'm going to guess that you're doing a default implicit conversion. Implicit conversions will frequently generate a warning message (harmless and preventable). Using an explicit conversion will usually eliminate the warning.

Please post your transformer expression.

Mike

[Thomas.B]

You can also use the Modify stage to do the conversion operation and use the NOWARN specification since the string to decimal conversion seems to be handled by DataStage :
https://www-01.ibm.com/support/knowledg ... sions.html

Ex :

Code: Select all

DEC01:decimal[10,0] = STR01

[adityavarma]

Mike,

Thank you for the reply.

I am using the below conversion function in the transformer stage

Code: Select all

StringToDecimal(trim(fromLkp.master_cust_number))

Thomas,

Thank you, i will use the modify stage and let you know

Thanks,
Aditya

[adityavarma]

Thank you Thomas .. it worked

I used the below specifications in Modify stage and the job completed successfully without warnings

Code: Select all

Specification = cust_num:decimal[10,0] = decimal_from_string(cust_num)
Specification = NOWARN

[chulett]

You can't use BBCode and then disable it at the same time. Okay, technically you can but what's the point? I fixed your previous post but not this most recent one so you can see the difference.

[adityavarma]

Thank you Craig,

My bad, i ignored the check boxes

[chulett]

Thanks, appreciate that.

[ray.wurlod]

I had the thought that maybe they (IBM) could put something like NOWARN into the properties of the Transformer stage, but my second, better, thought was that that would lead to too much lazy programming.

[Mike]

StringToDecimal is an explicit type conversion function for the transformer stage, so it won't generate a warning in going from varchar(10) to decimal(10,0) if your source string is nothing but numeric digits.

Did you isolate a specific example row that causes a warning?

Using NOWARN is likely masking a source data quality issue.

Your transformer expression with a simple Trim function isn't adequate to handle bad source data.

Mike