CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Saranya Jothiprakasam
Saranya Jothiprakasam

Reputation: 294

select case statement error in mssql

SELECT top 1 
    case 
        when VR = -99999.99 
        then 0 
        else cast((VR*1.732) as decimal(38,3)) 
    end 
FROM pseb.dbo.datasource 
where FeederID=5003
order by datetime desc

The above query is working fine, but I need to return varchar value '--' instead of returning 0

if I do like that 

SELECT top 1 
    case 
        when VR = -99999.99 
        then '--' 
        else cast((VR*1.732) as decimal(38,3)) 
    end 
FROM pseb.dbo.datasource 
where FeederID=5003
order by datetime desc

means it returns the following error:

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

please help me to solve it

Upvotes: 0

Views: 2341

Answers (1)

John Sobolewski
John Sobolewski

Reputation: 4572

The problem is that you are returning two different data types from the same column. The rule with SQL Server that numeric types take precedence over string types, i.e. in a situation like yours a string gets converted to a number, not the other way around.

So to solve this you can cast your number to a string.

One option is to do something like this:

SELECT top 1 
   case when VR = -99999.99 then '--' 
    else 
     cast
      ( 
        cast((VR*1.732) as decimal(38,3)
      ) 
     as varchar(50)) 
    end 
  FROM pseb.dbo.datasource where FeederID=5003 order by datetime desc

Upvotes: 5

Related Questions