Unable to convert the Varchar(9) to Numeric(9)

kshah9 · Post by **kshah9** » Mon Feb 21, 2011 2:17 am

I want to convert my Varchar(9) field to Numeric(9), but I am not able to do that in TFM stage with StringToDecimal Function. So plz help me in this...
Regards,
Kunal Shah

kkalyanrao@gmail.com · Mon Feb 21, 2011 3:11 am

Try this - StringToDecimal(InputColumn) and set output column data type to numeric(9)

kshah9 · Post by **kshah9** » Mon Feb 21, 2011 3:34 am

Thanks Kalyan, I did that, but still not able to get the desired result, and getting the Zeros in output. but the same functionality is working fine for other job. So appreciate the help in this matter...

priyadharsini · Post by **priyadharsini** » Mon Feb 21, 2011 5:11 am

Check the director for any warnings.

kshah9 · Post by **kshah9** » Mon Feb 21, 2011 6:18 am

Guys, Thanks for Help, the issue is resolved, it was the problem due to ^M character in Source File.

Thank you all for your help.

myukassign · Post by **myukassign** » Mon Feb 21, 2011 6:30 am

Alrt ...so here is the issue...

e.g. you are having a varchar '777777777' and you are converting and copying to decimal 9 using stringTodecimal and might have declared the legnth of your decimal field as 9 and scale 2.

now the string function will convert your function to 777777777.00

so see the total length of the converted value, it's 11.

Will it fit? it wont fit.... so datastage will show you 00000000 with a warning...

so now you know what to do... change the length and scale of target decimal field, it will work for sure.

chulett · Post by **chulett** » Mon Feb 21, 2011 9:25 am

They've already posted the issue and the fact that it was resolved, so unsure why you are bringing scale into the picture, it doesn't really seem relevant here.

myukassign · Post by **myukassign** » Mon Feb 21, 2011 10:45 am

chulett wrote:They've already posted the issue and the fact that it was resolved, so unsure why you are bringing scale into the picture, it doesn't really seem relevant here.

hmm..

Actully while I was replying and posting the reply the issue got solved...

well.. I faced a similar issue and all 000000 was because of the length and scale... so the intention was to help...