SQL Conversion failed when converting the varchar value to data type numeric

I'm having some difficulty with the following SQL query:

SELECT
SUM(ArTrnDetail.NetSalesValue) AS [Price],
'ExecuteSubReport(813,0,Job)' AS [LaborCost],
'ExecuteSubReport(815,0,Job)' AS [MaterialCost],
'ExecuteSymbolicMath(LaborCost+MaterialCost)' AS [TotalCost],
'ExecuteSymbolicMath(Price-(LaborCost+MaterialCost))' AS [Margin],
CASE
WHEN SUM(ArTrnDetail.NetSalesValue) = 0
THEN 0
ELSE 'ExecuteSymbolicMath(1-(TotalCost/Price))' <-- Where it's failing
END AS [MarginPct]


The ExecuteSubReport and ExecuteSymbolicMath are company functions. The ExecuteSymbolicMath basically strips unwanted chars like $ and commas, does the math and returns the result as a string. The end result for the column being a decimal(2 places) between 0-1. When I try to run the query I get an error saying that it can't convert varchar to numeric. I've tried just about everything I can think of. I've tried replace, convert, cast, str. Thank you very much for your help!

Continue reading...