How to concatenate a result of a case with other fields?

Question

Example:

select x+y+
    case
    when 2>1 then 'z'
    else 'w'
    end 
from table

Expected result: Xyz

The code above results in error. I couldn’t find anything like it... Admitting that they are all fields of the same type.

Answer 1

The items to be added or concatenated must be of the same type as the value to be returned by the excerpt case...when, I made an example using the cast to explain the desired type. In your case, the problem may be that some field is different from what is being returned by the expression case...when.

Considering your example, it would look like this:

  select cast(Coalesce(x,'') as varchar) + cast(Coalesce(y,'') as varchar) + 
    case
       when 2>1 then 'z'
       else 'w'
    end  
  from table

Coalesce(x,'') is to prevent error if the field is null.

In case it is text:

  select cast('x' as varchar) + cast('y' as varchar) + 
    case
       when 2>1 then 'z'
       else 'w'
    end 

Result: Xyz

In the case of integers:

  select cast('3' as int) + cast('4' as int) + 
    case
       when 2>1 then 4
       else 6
    end 

Result: 11

I did not put table in the last two examples, as it is not necessary to include the from table in such cases. Run to see if you solve the problem and match your need.