SQL - Using logical operators in a UDF case statement

Question

So it took a while for me to figure out how to create my first UDF but after I fixed it, I figured my next one would be a piece of cake. Unfortunately, it hasn't been the case. I'm pulling a field (ORIG_CLAIM, float) and I want to categorize that number. Here's my code:

CREATE FUNCTION [dbo].[fnOC_LEVEL](@ORIG_CLAIM float)
RETURNS nvarchar(255)
AS
BEGIN
    DECLARE @result as varchar(255);
    SELECT @result = case @ORIG_CLAIM
        when < 1000 then 'A_Under 1000'
        when >= 1000 and <= 4999.99 then 'B_1000-4999'
        when >= 5000 and <= 7499.99 then 'C_5000-7499'
        when >= 7500 and <= 9999.99 then 'D_7500-9999'
        when >= 10000 and <= 14999.99 then 'E_10000-14999'
        when >= 15000 and <= 19999.99 then 'F_15000-19999'
        when >= 20000 then 'G_Over 20000'
    END
    RETURN @result
END
GO

I'm getting the error "Incorrect syntax near '<'". Can anyone wee what I might be doing wrong?

Answer 1

I think you may have to specify the comparison values as float. For example:

 when < 1.0E3 then 'A_Under 1000'
 when >= 1.0E3 and <= 4.99999E3 then 'B_1000-4999'

etc.