In the source table, type of column Amount is Varchar(255).
I have tried to convert this into decimal value by following method.
Use function CAST( amount, ,‘DECIMAL(19,0)’) in query transform.
Just change type of target column to type DECIMAL(19,0) in query transform
Do we really need to apply cast function to convert character value to decimal ?
Results are same in both cases.
But by using cast function , sql created by dataflow is not fully pushdown. So I preferred to use option (2)
Is there any drawback of using option 2 ?
If you don’t address the data type conversion then you’ll get a conversion warning. I do not allow data type conversion warnings in my projects. If a developer submits a Dataflow for migration that has this then I’ll return it back to the developer to be fixed. Why? Because the warning clutters the results of the validation and eventually people stop looking at the warnings. This allows something that was really important to slip through.
As said above, try using the to_decimal() function. But are you absolutely 100% sure that the value will ALWAYS convert? If not, then you need to use a Validation transform to address those rows where the conversion will not work.
(BOB member since 2012-04-18)
(BOB member since 2007-09-12)