USERRR5 USERRR5 - 6 months ago 8
SQL Question

Case expression is not working properly in sql query

I have a table

Supplier
. I want to concat value of two columns
contact_number2
in table with comma separator and put it into an alias field named'contact'. I have used case expression 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


But the fourth condition is not working it is showing error. If I use the fourth condition only 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"

Answer

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;

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.)

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.

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.

Comments