Search code examples
sql-serverdatabasesybase

SQL: How to Store Integer value in CASE function in sql?

I'm working in a Sybase database.I want the 3rd 'WHEN' statement as Integer as output of Original value(Round((sum(value)*100),1)). How can I achieve this. Thanks in advance.

CASE  WHEN Round((sum(value)*100),1)<0 THEN 'Neg' 
      WHEN Round((sum(value)*100),1)=0 THEN 'N/A'
      WHEN Round((sum(value)*100),1)>0 THEN  Round((sum(value)*100),1)

END AS RCB

Note: here 3rd row indicating error as it is an integer.The error, I'm getting is : Error:- Data exception - data type conversion is not possible. In CASE expression, incompatible data type at result expression 3, ROUND(COALESCE ((SUM(MSTR.SUM.value)*100),0),1)

Secondly , I tried with Else statement below.

CASE  WHEN Round((sum(value)*100),1)<0 THEN 'Neg' 
      WHEN Round((sum(value)*100),1)=0 THEN 'N/A'
     --WHEN Round((sum(value)*100),1)>0 THEN Round((sum(value)*100),1)
      ELSE Round((sum(value)*100),1)
END AS RoRC,

But still getting the same error. Please help me out. Thanks.

Solution

  • You need to cast the number as varchar to match the type of other possible results of CASE statement

    CASE  WHEN Round((sum(value)*100),1)<0 THEN 'Neg' 
      WHEN Round((sum(value)*100),1)=0 THEN 'N/A'
      WHEN Round((sum(value)*100),1)>0 THEN  CAST(Round((sum(value)*100),1) as varchar)