Converting nvarchar to numeric

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!