Problem in Converting NVarChar(17) to Numeric(13,3)

Mukti · Post by **Mukti** » Sat Oct 20, 2007 4:55 am

I am mapping a MS SQL database column with NVarChar datatype of length 17 to a MS SQL database column of datatype Numeric of length13 and scale 3 simply using a transformer stage.

Source columns have some negative values. But in the target those -ve values are not coming.

for example:
Source column valu is 202120080.000- but the corresponding target value is .000

If anyone has some solution for this kindly revert as earlier as possible.

Thank you.
Regards
Mukti

ray.wurlod · Post by **ray.wurlod** » Sat Oct 20, 2007 2:23 pm

Welcome aboard.

Please explain your job design in more detail. In particular, are you applying any transformation to this field? If not, you probably need to investigate a function such as StringToDecimal() to convert the data type.

Parallel jobs do not manage all data type conversions automatically: if you have server job experience you may have become accustomed to this automatic conversion and expect it to happen. But it doesn't. Read the section about data types in the Parallel Job Developer's Guide

Mukti · Post by **Mukti** » Sat Oct 20, 2007 10:12 pm

ray.wurlod wrote:Welcome aboard.

Please explain your job design in more detail. In particular, are you applying any transformation to this field? If not, you probably need to investigate a function such as String ...

Hi Ray

Thanks for ur response.
Actually no transformation is applied to the field. It's just a direct mapping.
Source is a MS SQL database table. The required field is of NVarChar data type with field length 17. A transformer Stage is used to map to the target table which is also a MS SQL database table. In the target table the metadata for the field is Numeric having Length or precision 13 and scale value 3.
Source values for the field contains some negative values as I have mentioned earlier. But that value is not loading into the target. Instead of the exact value "202120080.000-" , ".000" is loaded.
I have tried to use "StringToDecimal" Type conversion function but not giving the desired result.

Waiting for ur response.
Regards
mukti

ray.wurlod · Post by **ray.wurlod** » Sun Oct 21, 2007 5:31 pm

Show the syntax you used with StringToDecimal().

Mukti · Post by **Mukti** » Sun Oct 21, 2007 9:56 pm

ray.wurlod wrote:Show the syntax you used with StringToDecimal(). ...

StringToDecimal(ext_s_e_b_item.SYSTEM_CALCULATED_OPENING_BALANCE)
mapped to output column SYSTEM_CALCULATED_BALANCE

ArndW · Post by **ArndW** » Sun Oct 21, 2007 10:35 pm

How did you declare the numeric column data type? What happens when your source data is just a smaller number, i.e. "12.34"?

Mukti · Post by **Mukti** » Sun Oct 21, 2007 11:42 pm

ArndW wrote:How did you declare the numeric column data type? What happens when your source data is just a smaller number, i.e. "12.34"? ...

The data type for the column is Numeric, length 13 and scale 3.
There is a value "0.533" in the source and the corresponding value of the target is ".000".
Similarly for source value "0.033-" (negative) target value is ".000"

ArndW · Post by **ArndW** » Sun Oct 21, 2007 11:45 pm

I'm not at a terminal with the docs right now, but could it be that 13 and 3 means total width of 13, of which 3 are decimals? What happens if you try 18.3 ?

Mukti · Post by **Mukti** » Mon Oct 22, 2007 12:44 am

ArndW wrote:I'm not at a terminal with the docs right now, but could it be that 13 and 3 means total width of 13, of which 3 are decimals? What happens if you try 18.3 ? ...

"18.3" is loading as "0000000018.300". But I have tried it with sequential file stage used as both source and Target. As I can't change the database.
I also tried with a value "18.3-" but that is loaded as "0000000000.000"

Mukti · Post by **Mukti** » Mon Oct 22, 2007 1:00 am

ArndW wrote:I'm not at a terminal with the docs right now, but could it be that 13 and 3 means total width of 13, of which 3 are decimals? What happens if you try 18.3 ? ...

"18.3" is loading as "0000000018.300". But I have tried it with sequential file stage used as both source and Target. As I can't change the database.
I also tried with a value "18.3-" but that is loaded as "0000000000.000"

ray.wurlod · Post by **ray.wurlod** » Mon Oct 22, 2007 1:27 am

Arnd meant that you should try Decimal[18,3]

ray.wurlod · Post by **ray.wurlod** » Mon Oct 22, 2007 1:28 am

Arnd meant that you should try Decimal[18,3]

Mukti · Post by **Mukti** » Mon Oct 22, 2007 2:24 am

ray.wurlod wrote:Arnd meant that you should try Decimal[18,3] ...

By changing to Decimal[18,3], The output is nothing new than before except with some more zeros to match the length.
Negative values are simply loaded as 000000000000000.000.

stefanfrost1 · Post by **stefanfrost1** » Mon Oct 22, 2007 3:30 am

you need to trim your input string and/or remove/change any unwanted characters.... Maybe your decimal point is invalid for example....

Mukti · Post by **Mukti** » Mon Oct 22, 2007 4:20 am

stefanfrost1 wrote:you need to trim your input string and/or remove/change any unwanted characters.... Maybe your decimal point is invalid for example....

I understand that. i have tried for those options. But the problem is with negative values. positive values are loading properly. When a negative number is encountered it may not be able to interpret.

So can anyone help me,by telling how to handel negative values.