Problem in Converting NVarChar(17) to Numeric(13,3)
Moderators: chulett, rschirm, roy
Problem in Converting NVarChar(17) to Numeric(13,3)
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
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
Muktiasha
Software Engineer
Software Engineer
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Problem in Converting NVarChar(17) to Numeric(13,3)
Hi Rayray.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 ...
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
Muktiasha
Software Engineer
Software Engineer
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
How did you declare the numeric column data type? What happens when your source data is just a smaller number, i.e. "12.34"?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
The data type for the column is Numeric, length 13 and scale 3.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"? ...
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"
Muktiasha
Software Engineer
Software Engineer
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 ?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
"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.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 ? ...
I also tried with a value "18.3-" but that is loaded as "0000000000.000"
Muktiasha
Software Engineer
Software Engineer
"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.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 ? ...
I also tried with a value "18.3-" but that is loaded as "0000000000.000"
Muktiasha
Software Engineer
Software Engineer
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 99
- Joined: Mon Sep 03, 2007 7:49 am
- Location: Stockholm, Sweden
you need to trim your input string and/or remove/change any unwanted characters.... Maybe your decimal point is invalid for example....
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
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.stefanfrost1 wrote:you need to trim your input string and/or remove/change any unwanted characters.... Maybe your decimal point is invalid for example....
So can anyone help me,by telling how to handel negative values.
Muktiasha
Software Engineer
Software Engineer