案例表达式在SQL查询中无法正常工作 [英] Case expression is not working properly in sql query

问题描述

我想用逗号分隔符连接supplier表的列，并将其放入名为"contact"的别名字段中.我用过一些检查空值的案例.假设如果contact_number2为null，则contact_number3将在别名字段中，反之亦然.这是我的查询

I want to concat columns of supplier table with comma separator and put it into an alias field named 'contact'. I have used cases for checking null values. Suppose if contact_number2 is null then contact_number3 will be in alias field and vice versa. Here is my query

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"

If I use the fourth condition then it works but if I use multiple condition then it doesn't work.The error is ORA-00932: inconsistent datatypes: expected NUMBER got CHAR 00932. 00000 - "inconsistent datatypes: expected %s got %s"

推荐答案

我认为您正在追求这样的东西:

I think you're after something like this:

select supplier_name,
       supplier_address,
       supplier_reference,
       contact_number1,
       contact_number2,
       contact_number3,
       case when contact_number2 is not null and contact_number3 is not null then contact_number2||','||contact_number3
            when contact_number3 is null and contact_number2 is null then '0'
            when contact_number2 is null then to_char(contact_number3)
            when contact_number3 is null then to_char(contact_number2)
       end as contact
from   supplier;

请注意，case表达式在满足的第一个条件处停止，因此您应确保条件的顺序正确. (例如，在我的查询中，当您到达当contact_number2为null时，contact_number3为空"时，由于先前的条件，我们已经知道contact_number3不能为null.)

Note that case expressions stop at the first condition that is met, so you should make sure that the conditions are in the right order. (Eg. in my query, by the time you get to the "when contact_number2 is null then contact_number3" we already know that contact_number3 can't be null due to the previous condition.)

此外，我已经将您的CONCAT转换为更常见(更灵活)的||形式.使用CONCAT，一次只能连接2个事物，而可以使用多个||来将各种字符串连接在一起.

Also, I have converted your CONCAT into the much more common (and more flexible) || form. With CONCAT, you can only concatenate 2 things at a time, whereas you can have multiple ||s to join various strings together.

出现错误的原因是，当将两个数字连接在一起时(尤其是在混合中添加逗号时！)，结果将是一个字符串.像您这样的CASE表达式对每个条件的结果使用相同的数据类型.

The reason why you got the error you did is because when you concatenate two numbers together (especially when you add a comma into the mix!) the result will be a string. CASE expressions like you to use the same datatype for the result of each condition.

这篇关于案例表达式在SQL查询中无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！