CODEWITHSUNDEEP
sqlsql-server-2008t-sql
codingManiac
codingManiac

Reputation: 1700

"Error converting data type nvarchar to float" with CASE WHEN Statement

I'm trying to run a query with the following as one of the select statements, and I keep getting the error "Error converting data type nvarchar to float." I've been converting VBA IIf statements to CASES and I can't seem to get the conversions right. fld2 is nvarchar(15) and fld1 is a float data type. I need help pinpointing why this error is being thrown.

 CASE WHEN (IsNumeric([fld2]) = 1) THEN Round(Convert(nvarchar,[fld2]) + 
   ' / ' + Convert(nvarchar,[fld1]),(Len(Convert(nvarchar,[theData])) -
  Charindex(Convert(nvarchar, [fld2]),'.'))) ELSE [fld2] END,

Upvotes: 1

Views: 4172

Answers (1)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107566

As is, your example would produce quite a funny expression for SQL server to evaluate. Let's substitute values for fld1, fld2, and theData as an example to see what you're trying to do:

[fld1]    = 42.0
[fld2]    = N'69.56'
[theData] = N'something'

(an N before a string makes it an nvarchar instead of varchar)

With substitutions, the resulting query would look like this:

CASE WHEN (IsNumeric(N'69.56') = 1) THEN 
    Round(Convert(nvarchar,'69.56') + ' / ' + Convert(nvarchar, 42.0),
        (Len(Convert(nvarchar,'something')) - Charindex(Convert(nvarchar, N'69.56'),'.')))  
ELSE 
    N'69.56' 
END

Since you don't need to convert an nvarchar to nvarchar explicitly, your query actually looks more like:

CASE WHEN (IsNumeric(N'69.56') = 1) THEN 
    Round(N'69.56 / ' + Convert(nvarchar, 42.0),
        (Len(N'something') - Charindex(N'69.56','.')))  
ELSE 
    N'69.56'
END

So there are a couple of problems:

  1. You're passing a varchar value into the ROUND() function, which expects a numeric value, not an expression
  2. The two paths of the CASE statement are returning different types

What I think your query should look like is:

CASE WHEN IsNumeric([fld2]) = 1 THEN 
    CONVERT(nvarchar, ROUND(CONVERT(float, [fld2]) / [fld1],
        (LEN([theData]) - CHARINDEX([fld2], '.'))))
ELSE 
    [fld2]
END

The above does the math and rounding on numeric results instead of strings, doesn't do any unnecessary conversions, and also returns the same type in both cases.

Upvotes: 2

Related Questions