How can I cast inside a case statement SQL-CodePudding

I am trying to change the value NULL to the string 'geen boetes' (Dutch for 'no fines') but it doesn't let me because I can't save a string in a numeric value type. I Tries every possible way to put CAST in there but I can't find the solution. Does anyone know what I do wrong?

SELECT CAST(s.naam AS varchar)
FROM spelers s;


SELECT 
    s.naam, 
    CASE    
        WHEN AVG(b.bedrag) IS NOT NULL 
            THEN ROUND(avg(b.bedrag), 2)
            ELSE CAST(AVG(b.bedrag) AS varchar) IS NULL THEN 0
    END as gemiddelde
FROM 
    spelers s 
LEFT OUTER JOIN 
    boetes b ON s.spelersnr = b.spelersnr
GROUP BY 
    s.naam
ORDER BY 
    s.naam

I tried to use the cast command to solve it.

CodePudding user response：

You cannot have an expression that returns a numeric value under some conditions and a string value under other conditions, the expression must always return the same type. The solution in the case is casting the avg.

select 
    s.naam, 
    case    
        when avg(b.bedrag) is not null 
            then cast(round(avg(b.bedrag), 2) as varchar) 
            else 'geen boetes' 
    end as gemiddelde
from 
    spelers s 
left outer join 
    boetes b on s.spelersnr = b.spelersnr
group by 
    s.naam
order by 
    s.naam