Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SeekingWisdom
Starting Member
24 Posts |
 Posted - 2009-02-19 : 09:20:39
|
| Hello, I am presently using an Aggregate task in my dataflow. There is a group by to allow me to sum up a Credit Amount by all the common fields. In a very simple fashion my input from a flat file looks like this. Cust# Inv# Credit Credit Amount is defined as DT_DECIMAL Amount with a length of 8 and a scale of 2. 12345 6789 -209812345 6789 -1400 The aggregat is defined as Cust# Group by Inv# Group by Credit Amount Sum The resulting ouput from the aggregate is: Cust# Inv# Credit Amount 12345 6789 -3498.00 Why is my result -3498.00 and not -34.98? Since this is my result I then try to re-align my decimal by diving b a 100 in a derived column as shown below: Derived Expression Data Type Scale Column Name New_Cred_Amt CRED_AMT / 100 DT_DECIMAL 2 The result of the divide is: New_Cred_Amt = 34.00 - Why are the cents dropped? Can anyone please help me with this? I do not know visual basic, but I was thinking that there must be some way to re-align the result from the aggregate using this language. Thank you in advance for your time and consideration of my dilemma. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-19 : 09:40:13
|
| whats the datatype of CRED_AMT? |
 |
|
|
SeekingWisdom
Starting Member
24 Posts |
Posted - 2009-02-19 : 10:24:52
|
| Thank you. The incoming column is CRED_AMT2 and has a data type of DT_NUMERIC. The Derived Column is New_CRED_AMT2 and a data type of DT_DECIMAL with a scale of 2. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-19 : 10:43:16
|
| First up, your incoing is DT_NUMERIC.Which is why (-2098) + (-1400) is giving you -3498Since you have defined CRED_AMT as DT_DECIMAL , it becomes -3498.00Are you dividing this value by 100 and storing in the same field? If yes, you should definitely see -34.98. If not, whats the data type of the second field? |
|
|
|
SeekingWisdom
Starting Member
24 Posts |
Posted - 2009-02-19 : 16:27:15
|
 Please note that I was able to obtain the desired result by dividing resulting column by 100.00 giving me -34.98. Thank you all for your desire to help. Each of you is invaluable to us new folks growing to love SSIS. All the best to you. |
|
|
|
|
|
|
Aggregate Sum and decimal dropped in result