Page 1 of 2
Problem in Converting NVarChar(17) to Numeric(13,3)
Posted: Sat Oct 20, 2007 4:55 am
by Mukti
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
Posted: Sat Oct 20, 2007 2:23 pm
by ray.wurlod
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
Problem in Converting NVarChar(17) to Numeric(13,3)
Posted: Sat Oct 20, 2007 10:12 pm
by Mukti
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
Posted: Sun Oct 21, 2007 5:31 pm
by ray.wurlod
Show the syntax you used with StringToDecimal().
Posted: Sun Oct 21, 2007 9:56 pm
by Mukti
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
Posted: Sun Oct 21, 2007 10:35 pm
by ArndW
How did you declare the numeric column data type? What happens when your source data is just a smaller number, i.e. "12.34"?
Posted: Sun Oct 21, 2007 11:42 pm
by Mukti
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"
Posted: Sun Oct 21, 2007 11:45 pm
by ArndW
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 ?
Posted: Mon Oct 22, 2007 12:44 am
by Mukti
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"
Posted: Mon Oct 22, 2007 1:00 am
by Mukti
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"
Posted: Mon Oct 22, 2007 1:27 am
by ray.wurlod
Arnd meant that you should try Decimal[18,3]
Posted: Mon Oct 22, 2007 1:28 am
by ray.wurlod
Arnd meant that you should try Decimal[18,3]
Posted: Mon Oct 22, 2007 2:24 am
by Mukti
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.
Posted: Mon Oct 22, 2007 3:30 am
by stefanfrost1
you need to trim your input string and/or remove/change any unwanted characters.... Maybe your decimal point is invalid for example....
Posted: Mon Oct 22, 2007 4:20 am
by Mukti
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.