CODEWITHSUNDEEP
sql-server
robot
robot

Reputation: 35

Getting a conversion failed when converting the varchar value 'string' to datatype int

I'm trying to print 'no data' for the null values in the tavg int datatype column but I keep getting a conversion failed error in the presence of the "then sum((tmin+tmax)/2)" portion. Any help?

    select
    Case 
    when (tavg > -59  and tmin is NULL and tmax is NULL ) then tavg
    when (tavg > -59 and tmin is NULL and tmax > -59) then tavg
    when (tavg is null and tmin >-59 and tmax > -59) then sum((tmin+tmax)/2)
    when (tavg is null and tmin is null and tmax is null) then 'No data'
    else cast(tavg as varchar(50)) end as 'avg_temp',
    case 
    when tmin is null then 'No data' else cast(tmin as varchar(50)) end as 'Minimum Temperature',

Upvotes: 1

Views: 121

Answers (1)

artm
artm

Reputation: 8584

In a case when all return types should be the same otherwise SQL will try to cast all return types to the highest precedence. The error you are getting is becuase SQL is trying to convert 'No Data' to int (higher precedence). If you return varchar from all cases it should work:

select
    Case 
    when (tavg > -59  and tmin is NULL and tmax is NULL ) then cast(tavg as varchar(50))
    when (tavg > -59 and tmin is NULL and tmax > -59) then cast(tavg as varchar(50))
    when (tavg is null and tmin >-59 and tmax > -59) then cast(sum((tmin+tmax)/2) as varchar(50))
    when (tavg is null and tmin is null and tmax is null) then 'No data'
    else cast(tavg as varchar(50)) end as 'avg_temp',
    case 
    when tmin is null then 'No data' else cast(tmin as varchar(50)) end as 'Minimum Temperature',

Data Type Precedence (Transact-SQL)

Upvotes: 4

Related Questions