Case Statement with Different Data Type

Case Statement with different data type

A case statement can only return one data type. So convert the numbers to strings:

SELECT CASE
  WHEN fourthlevel.case_type IN ('Complaint')
  THEN
     (SELECT cast(COUNT(*) as varchar2(255))
        FROM work_days1
       WHERE     work_days1.business_date > fourthlevel.cdate
             AND work_days1.business_date <=
                    COALESCE (fourthlevel.close_date, SYSDATE))
  WHEN fourthlevel.case_type IN ('Enquiry')
  THEN
     (SELECT cast(COUNT(*) as varchar2(255))
        FROM work_days1
       WHERE     work_days1.business_date > fourthlevel.create_date
             AND work_days1.business_date <=
                    COALESCE (fourthlevel.close_date, SYSDATE))
  WHEN fourthlevel.case_status = 'Cancelled'
  THEN
     'N/A'
END AS sla_days
FROM fourthlevel

Alternatively, you could return NULL when the two conditions do not match.

t-sql Different datatype possible in a case?

You have to convert all of your case expressions to varchar. SQL is deciding to case the field as int so 'toto' is invalid. If all expressions are converted to varchar this error should be solved.

http://blog.sqlauthority.com/2010/10/08/sql-server-simple-explanation-of-data-type-precedence/

Different data types using case when in SQL

If you try it, you will get a type conversion error. case is an expression that returns a single value, with a well-defined type.

When one branch of a case returns a number, then the value is a number -- these are the type conversion rules for SQL. The string 'greater than 5' cannot be converted, so you get an error.

So, just do an explicit conversion:

select (case when val <= 5
             then cast(val as varchar(255))
             else 'greater than 5'
        end) as val_category
from table

JOIN different data types using case statement MS SQL Server?

While I totally agree with Gordon about fixing your data, or possibly even the structure you have it all there. Just a syntax error. Please realize this is not going to perform or scale very well.

select * 
from table1 t1
JOIN table2 t2
on case t1.jobtype
    when 'a' then 1
    when 'b' then 2
    when 'c' then 3
    when 'd' then 4
    end = t2.jobtype

--EDIT--

Not a big deal at all. Thanks for posting ddl and sample data. You just need to reverse the case expression here.

select * 
from table1 t1
JOIN table2 t2
on case t1.jobtype
    when 1 then 'a'
    when 2 then 'b'
    when 3 then 'c'
    when 4 then 'd'
    end = t2.jobtype

Using a CASE statement in ORDER BY clause with different data types

The underlying issue that @Damien_The_Unbeliever states perfectly in the comments is:

A single CASE expression has to produce values of one data type. So if you have THENs that produce values of different data types, the system uses the precedence rules to decide which ones to convert.

You can replicate your CASE statement to work around this, where each CASE returns a single value/data type. This would be better than converting all values to VARCHAR as suggested in the other answer, which should also perform better (you will have to test):

So new ORDER BY clause will look like:

ORDER BY CASE WHEN @SortOr = 1 THEN col1 
         END ,
         CASE WHEN @SortOr != 1 THEN col2
         END -- If you need DESC it goes after END

Java switch case statement is returning different type of values(different data-type) and is assigned to same variable of type var

The type of output is determined at compile time as something that is Serializable, Comparable and Constable, because that's the closest common ancestor to all of the result.

The object assigned to the variable is determined at runtime is one of String, Character, Double or Integer.

---

The actual compile type is Serializable & Comparable<? extends Serializable & Comparable<?> & Constable> & Constable (My IDE told me that!).

Convert data type to text for case statement

consider isnull()

CASE WHEN isnull([OrderID], '') = '' THEN 'N'   
     ELSE cast([Order_ID] as varchar(300)) END AS [Order_ID]

How to change Data Type with using Case Statement

You may need the following:

with gift_clubs(gift_club_end_date) as
(
    select '20160802' from dual union all
    select '00000000' from dual union all
    select null       from dual
)
select case
        when gift_club_end_date = '00000000'
            then to_date(null)
        when gift_club_end_date is null
            then to_date(null)
        when gift_club_end_date = ' '
            then to_date(null)
        else
            to_date(gift_club_end_date, 'yyyymmdd')
       end
from gift_clubs

This considers the case of null and of a string containing a single space; you probably need only one of these, so edit the statement accordingly.

---

Related Topics