oracle 具有不同数据类型的 Case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24770739/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Case Statement with different data type
提问by Heisenberg
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:
我正在尝试针对特定条件返回 SLA 天数。但是对于特定条件，我希望它返回不同的数据类型。当前代码如下：
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:
我希望它返回 where case_status = 'Cancelled' return 'N/A'。我知道我不能这样做，但我将包含代码以便更容易理解：
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?
我该怎么做呢？
回答by Gordon Linoff
A casestatement can only return one data type. So convert the numbers to strings:
一条case语句只能返回一种数据类型。因此，将数字转换为字符串：
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 NULLwhen the two conditions do not match.
或者，您可以NULL在两个条件不匹配时返回。
