As a Dynamics developer, I'm frequently asked to divide by the USCATVLS fields in the IV00101 table. Since they're CHAR types, there is often no validation on empty values or numeric values. The funtion below will take two VARCHAR values and divide them, returning '0' if there is some validation issue. The code at the end is a test script.
IF OBJECT_ID (N'dbo.f_FP_divide') IS NOT NULL
DROP FUNCTION dbo.f_FP_divide
GO
CREATE FUNCTION f_FP_divide
/*
select
dbo.f_FP_divide('0','0'),
dbo.f_FP_divide('a','0'),
dbo.f_FP_divide('0','a'),
dbo.f_FP_divide('2','0'),
dbo.f_FP_divide('0','2'),
dbo.f_FP_divide('15','2')
*/
(
@vchrNum1 as varchar(20),
@vchrNum2 as varchar(20)
)
RETURNS numeric(19,5)
AS
BEGIN
declare @nNum1 as numeric(19,5),
@nNum2 as numeric(19,5)
if isnumeric(@vchrNum1) = 0 begin
return 0
end
if isnumeric(@vchrNum2) = 0 begin
return 0
end
select @nNum1 = @vchrNum1,
@nNum2 = @vchrNum2
if @nNum2 = 0 begin
return 0
end
return @nNum1 / @nNum2
END
GO
To test:
select amtwork.sopnumbe, inv.itemnmbr,
amtwork.qtyfulfi,
inv.uscatvls_4,
dbo.f_FP_divide(AMTWORK.QTYFULFI,INV.USCATVLS_4) as CARTONS
from sop30300 amtwork
join iv00101 inv on inv.itemnmbr = amtwork.itemnmbr