Varchar to Decimal issue

challard1 · Post by **challard1** » Thu Sep 01, 2016 3:12 pm

I have a file with a varchar numeric field containing a maximum of 6 caracters (for example 3.45 or 23.45 or 234.56). I need to load this field in a DB2 table where the field is defined (decimal 5,2).

I have tried many possibilities using stringtodecimal function with no success mainly due to the decimal point in the source field I believe.

What is the easiest way to succeed ?

Thanks in advance for your help.

chulett · Post by **chulett** » Thu Sep 01, 2016 4:26 pm

Please show us the exact syntax you are using and the error / issue you are seeing.

ray.wurlod · Post by **ray.wurlod** » Fri Sep 02, 2016 4:07 am

Use a format string with the function, to describe the format of the string (including, presumably, the European decimal placeholder).

challard1 · Post by **challard1** » Fri Sep 02, 2016 4:19 am

Here is the exact syntax:

Input variable ADHPCT
Defined Varchar 6
Value : 98.27

Stage variable svAdhpct
Defined Decimal 5,2

Transformation variable svAdhpct
StringToDecimal(link.ADHPCT)

Output variable ADHPCT
Defined Decimal 5,2
Result in DB2 table : 0.00

chulett · Post by **chulett** » Fri Sep 02, 2016 6:21 am

As Ray noted, use a format string in your function call.

FranklinE · Post by **FranklinE** » Fri Sep 02, 2016 8:57 am

Blunt object through window approach: Stage variable svADHchar Char(6) -- StringToString (ADHPCT) (set PADCHAR to 0x20); svAdhpct -- StringToDecimal (Trim(svADHchar)).

Have I mentioned yet today that putting numeric values in VarChar fields is a royal pain in the arse?

challard1 · Post by **challard1** » Sat Sep 03, 2016 7:22 am

Thanks Craig and Franklin.
I have tried Franklin advice ... with no success for now.
Got same results. Will investigate further.

FranklinE · Post by **FranklinE** » Thu Sep 08, 2016 12:42 pm

Just remembered that you indicated the output value as 0.00. Sometimes this is from a type conversion failure that you might miss because it's a warning level log entry.

Try increasing the size of the decimal ADHPCT to 6,2 or more. Stage variables are usually defaulted to signed, and that might be why the conversion is defaulting to zero.

Also, insert a peak. What you see on DB2 may not be what you see in the buffer.

challard1 · Post by **challard1** » Sun Oct 23, 2016 6:22 pm

Finally resolved the issue. The problem was the input file. It was a DOS format file, so the last field of the record ADHPCT was not map properly. After adding the Record delimiter field = DOS format, problem was fixed.
Thanks everyone for your help on this topic.