Case Statement with different data type

case, oracle, sql

Solution

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.

Problem

I am trying to return SLA days for particular conditions. However for a specific condition I want it to return a different data type. Current code is as follows: ``` SELECT CASE WHEN fourthlevel.case_type IN ('Complaint') THEN (SELECT COUNT (*) 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 COUNT (*) FROM work_days1 WHERE work_days1.business_date > fourthlevel.create_date AND work_days1.business_date <= COALESCE (fourthlevel.close_date, SYSDATE)) END AS sla_days FROM fourthlevel ``` I want it to return for where case_status = 'Cancelled' return 'N/A'. I know i cant do it like this but i will include the code so it is easier to understand: ``` SELECT CASE WHEN fourthlevel.case_type IN ('Complaint') THEN (SELECT COUNT (*) 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 COUNT (*) 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 ``` How do i do this?

Original source