具有不同数据类型的案例陈述 [英] Case Statement with different data type

问题描述

我正在尝试退还特定条件下的SLA天数.但是对于特定条件，我希望它返回不同的数据类型.当前代码如下:

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

我希望它在case_status ='Cancelled'返回'N/A'的情况下返回.我知道我无法做到这一点，但是我将包含代码，因此更易于理解:

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

我该怎么做?

推荐答案

case语句只能返回一种数据类型.因此，将数字转换为字符串:

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

或者，当两个条件不匹配时，您可以返回NULL.

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

这篇关于具有不同数据类型的案例陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！