I have a situation where the data type of the column is VARCHAR(500) and I am casting to DECIMAL(16,2) for a SUM() but I need to add a CASE statement/expression to alter to a text string if certain conditions are met. I have tried the below syntax, but I get an error of

Msg 8114, Level 16, State 5, Line 7
Error converting data type varchar to numeric.

I can not change DDL - so what change in this query needs to be made in order for the case statement to execute succesfully?

DECLARE @TestData Table (
        id int, 
        rep varchar(500), 
        amt varchar(500))

INSERT INTO @TestData (id, rep, amt) 
(1, 'John', '200.00'), 
(2, 'John', '300.00'), 
(3, 'John', '10,00'),
(4, 'Gabe', '245.00'), 
(5, 'Gabe', NULL), 
(6, 'Gabe', '0.00')

SELECT id, John, Gabe
        SELECT id, rep, TotalAmt = CASE WHEN id IN (1, 4, 6) THEN 'RC(1000)' 
        ELSE SUM(CAST(amt AS DECIMAL(16,2))) END FROM @TestData
        GROUP BY id, rep
     ) x
PIVOT ( SUM(x.TotalAmt) FOR rep IN ([John],[Gabe])) As SalesRep
  • 4
    Please add the output that you want. Right now you're doing a sum of a VARCHAR value and it isn't clear to me what you're trying to accomplish. – Joe Obbish Jun 18 '17 at 2:30
  • 6
    Why is the amount for id 3 using a comma and the others using a decimal point? – Tom V Jun 18 '17 at 7:52
  • 4
    Why is the GROUP BY and SUM() there? id seems to be unique. – ypercubeᵀᴹ Jun 18 '17 at 8:07
  • Could you filter out the IDs you want that specific value for in another query? You would be splitting the query in 2, but it might be cleaner to do that. – Nestor Jun 18 '17 at 11:20
  • 1
    Your CASE results in a numeric column and thus tries to convert 'RC(1000)' to a decimal. – dnoeth Jun 18 '17 at 16:40

You cannot have a column that holds both VARCHAR(500) and DECIMAL(16,2). The query initially gets a DECIMAL(16,2) value and thus sets the TotalAmt column in the subquery as a DECIMAL(16,2) column; when it generates a value a 'RC(1000)', it tries and fails to convert that to DECIMAL(16,2).

You can make the subquery work by converting the results of the SUM to VARCHAR:

      SELECT id, rep, TotalAmt = CASE WHEN id IN (1, 4, 6) THEN 'RC(1000)' 
      GROUP BY id, rep
     ) x

However, then your SUM(x.TotalAmt) in your PIVOT statement will fail. What should the results of the following be?

'RC(1000)' + 300.00 + 10.00
'RC(1000)' + 0.00 + 'RC(1000)'

It seems likely there's problem in the logic of what you're trying to do here, given the above.

NOTE: If RC is a function you're trying to call, you'll need to actually call the function instead of inserting the call into your data, or you'll have to use dynamic SQL to call it at some later time in your process.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.