Cast error with Case Statement

[PNeems]

I am running the below case statement and getting the below error. I have no idea why its not working, it seems so simple, but i am not trying to convert anything. Does anyone know what the problem is?

CASE
WHEN [AMOUNT] >= 0 AND [ATCODE] = 'A'
THEN 'Asset'

Conversion failed when converting the varchar value 'Asset' to data type int.

 

[gmmastros]

Your problem is elsewhere.

Take a look at this example (Copy/paste to query window and run it).

Select Case When 1=1 Then 'Asset' Else 0 End

You will get this...

[!][tt]Conversion failed when converting the varchar value 'Asset' to data type int.[/tt][/!]

The problem is, depending on the evaluation, the code is returning 'Asset' (which is a string) or 0 (which is an integer). When you write a case/when statement, it's usually best to make sure the datatypes (for each branch of code execution) returns the same data type.

Obviously, you cannot convert 'Asset' to integer, but you can convert integer to varchar, like this...

Select Case When 1=1 Then 'Asset' Else [!]Convert(VarChar(20),[/!] 0[!])[/!] End

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PNeems]

George,
Thanks for the help. I dont think that is the issue. I dont have an else statement in my code. This it the whole code

WHEN [AMOUNT] >= 0 AND [ATCODE] = 'A'
THEN 'Asset'
WHEN [AMOUNT] < 0 AND [ATCODE] = 'A'
THEN 'Liability'
WHEN [AMOUNT] >= 0 AND [ATCODE] = 'I'
THEN 'Income'
WHEN [AMOUNT] < 0 AND [ATCODE] = 'I'
THEN 'Expense'
WHEN [ATCODE] = 'E'
THEN 'Equity'

There is no else statement

 

[gmmastros]

It doesn't have to be in an Else statement to cause a problem. Ex:

Select Case When 1=1 Then 'Asset' 
            When 1=2 Then 0 
            When 3=4 Then 'Liability' 
       End

There's no else statement and you still get the same error. Since you say "this is the whole code", then it may not be a data type mis-match in the case/when branches. You state that there is no ELSE, which is fine. If you have a row that does not match any of the conditions, you will get NULL, which does not pose any sort of data type problem.

Truth is, I don't see anything wrong with your code. I still think the real issue is somewhere else (because I don't see any problems ).

Do you have a corresponding END in your code that matches up with the CASE. Each CASE should have exactly one END. Without seeing more, I'm afraid I can't help you any further.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom