I have a table
SELECT supplier_Name, supplier_Address, supplier_reference, contact_Number1,
(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
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;
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.