CODEWITHSUNDEEP
sql-servercasecase-statement
user3345212
user3345212

Reputation: 131

SQL Server Convert in Case Statement

I have a column called compositeRate, it is a decimal(10,2) datatype. I am writing a Select statement that should return empty string if compositeRate is 0.00 however I am getting this error: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.

This is my case statement

select CASE WHEN compositeRate = 0.00 THEN '' else compositeRate from table

Could you please let me know where should I write convert in my case statement?

Upvotes: 2

Views: 44578

Answers (2)

Joel
Joel

Reputation: 1

I looked at this thread to solve a similar problem of my own. I'm using CASE statements to convert NULL values generated from a JOIN in the overall query.

Particularly, I'm looking at grades, which are housed as DECIMALs within the tables with which I am working.

Here's what I came up with (yes, it's just a snippet of the whole query):

CASE

WHEN B.[DecimalGrade] IS NULL THEN CAST ('' AS NVARCHAR (1))

ELSE CAST (B.[DecimalGrade] AS NVARCHAR (4))

END AS DecimalGrade

Upvotes: 0

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391446

You will need to convert compositeRate to a string in the else part:

SELECT CASE 
          WHEN compositeRate = 0.00 THEN CAST('' AS Varchar(20))
          ELSE CAST(compositeRate AS VARCHAR(20))
       END          AS compositeRate 
  FROM table

or use CONVERT with the appropriate values.

Right now you have a CASE expression that returns either a string or a number, and you have to decide which is the right type to return. One CASE expression must return one type.

Upvotes: 6

Related Questions