Conversion error calling conversion routine decimal_from_str

[MrBlack]

I've read the other threads already on datastage so I know this is a popular error, but I'd thought I'd just post again in case there were any fresh ideas how to get rid of the warning:

Code: Select all

Conversion error calling conversion routine decimal_from_string data may have been lost

Input source is a varchar(11)
Destination is a decimal(11,0)

I've tried several other datatypes and combinations. I've trimmed, I've substringed, I've null handled, I've done everything that I can think of and yet I can't get rid of this warning.

The data is flowing right, I can i live with the warning being in the logs, but I don't wan't to have this known warning count against me in my stats of jobs running ok.

[ray.wurlod]

I suspect that the warning is generated from the metadata. Decimal(11,0) implies a string of 13 characters (including sign and decimal placeholder), so that the metadata generates a warning because your source is smaller than this. Can you try changing the source data type to VarChar(13) either in the metadata or in a Modify stage?

[MrBlack]

I randomly stumbled on the solution, long after giving up hope and I had moved onto to other work. The FTP stage doesn't have the ability to skip the first row if the CSV has headers (really annoying that you can do it in a sequential stage but not in FTP stage). So the function was complaining about not being able to convert the header row into a decimal (which makes sense). So in my transformer I set a condition to reject the first row (@INROWNUM > 1) and that solve my warning message.

So now I'm able to do this on my nice clean canvas:

Code: Select all

[sFTP] -> [Transformer] -> [Database]