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

Question

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!

Hint: What's the type of the CASE expression? If it's numeric, how is 'ExecuteSymbolicMath(1-(TotalCost/Price))' going to be treated as a numeric value? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 21, 2021 at 18:43
You are passing in strings, not functions. I believe you want to remove the ' and ' around the expression you want SQL to parse as an expression — Conor Cunningham MSFT
– Conor Cunningham MSFT, Commented Jan 21, 2021 at 18:51

Gordon Linoff · Accepted Answer · 2021-01-21 18:51:56Z

5

A SQL expression has exactly one type. A case expression is no different.

According to the rules of SQL, numbers trump strings, so SQL tries to convert a string to a number -- and that is what is happening.

Just use '0' instead of 0 in the case expression:

(CASE WHEN SUM(ArTrnDetail.NetSalesValue) = 0 
      THEN '0'
      ELSE 'ExecuteSymbolicMath(1-(TotalCost/Price))'  <-- Where it's failing
  END) AS [MarginPct]

Or, if you actually want a calculation, get rid of all the single quotes:

(CASE WHEN SUM(ArTrnDetail.NetSalesValue) = 0 
      THEN 0
      ELSE ExecuteSymbolicMath(1-(TotalCost/Price)) 
 END) AS [MarginPct]

answered Jan 21, 2021 at 18:51

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

johnreeves Over a year ago

I just came to this conclusion. This fixed my problem. I'll give you credit. Thank you all!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related