Conversion Error

[DrewConn]

I am having trouble with the following statement:

select top 100 [decision_score],
case when [decision_score] between 620 and 639 then '620-639'
else [decision_score]
end as ScoreBand
from tblCMSImain
where [initiation date] > '01/01/04'

Getting error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '620-639' to a column of data type int.

Howerver when I cast the string to a varchar I still get the same error

 

[SQLBill]

What are you trying to do? What results do you want to see?

It appears that [decision_score] is a column with datatype INT. You can not put a string into an INT column. Here's what your results would look like:

decision score ScoreBand
600 600 --this is an integer
621 620-639 --this is a string (varchar)

A column must be all the same datatype.

-SQLBill

 

[donutman]

The reason for the error is that the case statement wants all results to be of the same type.

[Blue]SELECT[/Blue] [Blue]TOP[/Blue] 100 [decision_score][Gray],[/Gray]
    [Blue]CASE[/Blue] [Blue]WHEN[/Blue] [decision_score] [Blue]BETWEEN[/Blue] 620 [Gray]AND[/Gray] 639
         [Blue]THEN[/Blue] [red]'620-639'[/red] 
         [Blue]ELSE[/Blue] [Fuchsia]Cast[/Fuchsia][Gray]([/Gray][decision_score] [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]20[Gray])[/Gray][Gray])[/Gray]
    [Blue]END[/Blue] [Blue]AS[/Blue] ScoreBand 
[Blue]FROM[/Blue] tblCMSImain
[Blue]WHERE[/Blue] [initiation date] [Gray]>[/Gray] [red]'01/01/04'[/red]  
[code]
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[DrewConn]

That code worked , I was tring to cast the char field instead of the else result field.

Thanks.