DSXchange

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.

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

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

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

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

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?

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

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.

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.