我想连接列supplier
使用逗号分隔符创建表并将其放入名为“contact”的别名字段中。我使用过检查空值的案例。假设如果contact_number2
则为空contact_number3
将在别名字段中,反之亦然。这是我的查询
SELECT supplier_Name, supplier_Address, supplier_reference, contact_Number1,
contact_number2, contact_number3,
(case when contact_number2 is null then contact_number3
when contact_number3 is null then contact_number2
when contact_number3 is null and contact_number2 is null then 0
-- when contact_number2 is not null and contact_number3 is not null then CONCAT(CONCAT(contact_number2,','), contact_number3)
end) as contact
FROM SUPPLIER
如果我使用第四个条件,那么它可以工作,但是如果我使用多个条件,那么它就不起作用。错误是ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 - "inconsistent datatypes: expected %s got %s"
第三种情况需要 VARCHAR,而您提供了 INT,因此它返回错误。变化是我将 0 替换为“0”。尝试这个:
SELECT supplier_Name,supplier_Address,supplier_reference,contact_Number1,contact_number2, contact_number3,
(case
when contact_number2 is null then contact_number3
when contact_number3 is null then contact_number2
when contact_number3 is null and contact_number2 is null then '0'
when contact_number2 is not null and contact_number3 is not null then CONCAT(CONCAT(contact_number2,','), contact_number3)
end)
as contact
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)