October 15, 2015 at 8:54 am
The number_value column from the source should be multiplied with two multiplier columns. The multiplier columns might not have values for some records i.e. there are null values. The logic is to multiply only when there is value in a multiplier column for that particular record. We always end up with the value being rounded off. The query used is as below.
Selectnumber_value
* case when multiplier1 is null then 1 else multipier1 end
* case when multiplier2 is null then 1 else multiplier2 end as fact_val_nbr_mult
From table
We have tried to explicitly cast the columns to
•Decimal(38,14)
•Float
•Money
•Real
•String
Each datatype results in the number being rounded off but at different digits after the decimal point.
The rounding off does not happen if the value is used directly in the query, but only when the columns are multiplied (Eg. Select cast(-32518.66106104026559 as decimal(38,14)). We tried to convert it to string and then convert it back to DT_NUMERIC(38,14) in SSIS but the issue still persists.
The number_value, the multiplier1 and the multiplier2 are all decimal(38,14) in the database.
October 15, 2015 at 8:58 am
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/03/29/564110.aspx
Maybe this will explain the behaviour
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply