SQL Server: casting the column datatype

SQL Server 2005/ 2008

I have kept a CASE condition for a SQL query which results Float /Numeric Value. One of the CASE condition is to say N/A. I kept the piece of code.

For column : Compliance - Possible values could be N/A,100.0,99.1 ... ( nvarchar, float ).

select 
    x.MemberName, x.DOB, x.F开发者_如何学GoilePath, 
    x.Medication, x.NDC, x.Directions, 
    x.Name, x.Strength, x.GenericName,
    x.QtyOrdered, x.DaysSupply, x.DateFilled, 
    CASE WHEN x.test = 0 THEN 'N/A' 
         WHEN compliance > 100.0 THEN '100.0' 
         ELSE CAST(FLOOR(compliance * 10)/10.0 AS DECIMAL(3, 1)) 
    END AS [Compliance]

Above syntax spills error as ..

Msg 8114, Level 16, State 5, Line 10

Error converting data type varchar to numeric.

How should I type cast the field ?

CASE is an expression that returns a single result and regardless of the path it always needs to result in the same data type (or implicitly convertible to the same data type). Try:

...
CASE 
    WHEN x.test = 0           THEN 'N/A' 
    WHEN compliance > 100.0   THEN '100.0' 
    ELSE CONVERT(VARCHAR(5), CAST(FLOOR(compliance *10)/10.0 AS DECIMAL(3,1))) 
    END AS as [Compliance];

You're not consistent in your CASE statement - you need to provide the same data type as result for all options.

 CASE WHEN x.test = 0 THEN 'N/A' 
     WHEN compliance > 100.0 THEN '100.0' 
     ELSE CAST(FLOOR(compliance * 10)/10.0 AS DECIMAL(3, 1)) 
 END AS [Compliance]

Since the first two options return a string ('N/A' or '100.0'), your last option also must return a string value:

 CASE WHEN x.test = 0 THEN 'N/A' 
     WHEN compliance > 100.0 THEN '100.0' 
     ELSE CAST(CAST(FLOOR(compliance * 10)/10.0 AS DECIMAL(3, 1)) AS VARCHAR(20))
 END AS [Compliance]