Hello,
I am attempting to divide two columns from as an expression but running into some formatting issues. I attempted using SQL (zero prior knowledge) and my new column does show the result I am looking for but when I kick off a build I receive this error message:
“Inner Exception: Arithmetic overflow error converting nvarchar to data type numeric.”
Would anyone be able to tell me where I am going wrong on my expression?
case when Onhand_Qty = ‘NULL’ then ‘0’ else (convert(decimal(6,2),[Ext_Cost]))/[Onhand_Qty] end
Thanks!
-Andrew
Welcome @Andrew_Singer to the forum.
You appear to be converting [Ext_Cost] to be decimal but limiting it to 6 numbers with 2 decimals, therefore only 4 numbers in front of the decimal so values up to 9999. I suspect you have a value that’s larger than that. We support up to 6 decimal places and we typically use decimal(18,6) data type in our ETL processes. I would use decimal(18,6) or the float data type.
case when Onhand_Qty = ‘NULL’ then ‘0’ else (convert(decimal(18,6),[Ext_Cost]))/[Onhand_Qty] end
Ah, got it… Works perfectly now, thank you!
1 Like