Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float

0
votes

I wrote a code to convert the cost into EURO according to the currency scenario in SQL Server. However I get this error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

Help me please. 

SELECT 
    WORK.dbo.['Parts Detail$'].PartNumber,
    WORK.dbo.['Parts Detail$'].COST,
    CASE 
       WHEN WORK.dbo.['Parts Detail$'].CURRENCY = 'GBP' 
          THEN (WORK.dbo.['Parts Detail$'].COST * 1.17)
       WHEN WORK.dbo.['Parts Detail$'].CURRENCY = 'USD' 
          THEN (WORK.dbo.['Parts Detail$'].COST * 0.87)
       WHEN WORK.dbo.['Parts Detail$'].CURRENCY = 'EUR' 
          THEN (WORK.dbo.['Parts Detail$'].COST * 1)
       ELSE ('NO CURRENCY AVAILABLE') 
    END AS CIFCOST
FROM
    ['Parts Detail$']
2
sql-server
CASE in T-SQL is an expression that returns a single, atomic value - and all paths and all possible values being returned must be the same datatype - this is NOT the case here! In three cases, you return a FLOAT - but in the ELSE clause, you return a string. SQL Server will pick the datatype with the highest datatype precedence - here FLOAT - and will try to convert all other return values to that type - hence the error message .. - marc_s

2 Answers

0
votes

Your ELSE clause may be causing the problem. Just return NULL:

       ELSE NULL END AS CIFCOST
0
votes

you need to change ELSE ('NO CURRENCY AVAILABLE') 

SELECT WORK.dbo.['Parts Detail$'].PartNumber,
       WORK.dbo.['Parts Detail$'].COST,
       CASE 
            WHEN WORK.dbo.['Parts Detail$'].CURRENCY = 'GBP' THEN (WORK.dbo.['Parts Detail$'].COST*1.17)
            WHEN WORK.dbo.['Parts Detail$'].CURRENCY= 'USD' THEN (WORK.dbo.['Parts Detail$'].COST*0.87)
            WHEN WORK.dbo.['Parts Detail$'].CURRENCY='EUR' THEN (WORK.dbo.['Parts Detail$'].COST*1)
            ELSE null END AS CIFCOST
FROM ['Parts Detail$']

you can't contain stirng value in float type