coalesce on sample database not work

Question

Here I'm using the SAMPLE Database of DB2.

I'm trying to use coalesce to change the null values in MIDINIT column of EMPLOYEE table but I still get the null values result after I run the code.

SELECT COALESCE (MIDINIT, 'XX') AS MIDINIT
FROM EMPLOYEE;

run result

Answer 1

what is your error?

if MIDINIT is not a char or a varchar (example is integer), your coalesce replace a integer value by char value and system SQL cant choice the type

if MIDINIT is not a char or a varchar you should be

SELECT COALESCE (cast(MIDINIT as varchar(200)), 'XX') AS MIDINIT
FROM EMPLOYEE;

or if MIDINIT is numeric

SELECT COALESCE (MIDINIT, 0) AS MIDINIT
FROM EMPLOYEE;

you can use ifnull function to replace coalesce if you want ( if MIDINIT is char or varchar):

SELECT ifnull(MIDINIT, 'XX') AS MIDINIT
FROM EMPLOYEE;