COALESCE in DB2 returning empty-CodePudding

In DB2, I'm trying to get the first non-empty field of 3:

COALESCE(KONTAKT_MOB,KONTAKT_TLF,KUNDE_TLF_MOB)

Full SQL:

SELECT   KUNDE_PART_REF, KONTAKT_PART_REF, KUNDE_DIVISJON_KODE, 
         KUNDE_EMAIL_KUNDEKORT, KUNDE_TLF_MOB, KONTAKT_TLF, 
         KONTAKT_MOB, KUNDE_NAVN, KONTAKT_NAVN, KUNDE_PERS_ORG_KODE, coaleSce(KONTAKT_MOB,KONTAKT_TLF,KUNDE_TLF_MOB) as tlf
FROM     G00V.G79_KUNDE_KONTAKT_INFO_NL_CBT
where KUNDE_DIVISJON_KODE in ('L') and KUNDE_PERS_ORG_KODE not in ('O');

KONTAKT_MOB is a varchar with length 15. It's never null, but it can be empty.
KONTAKT_TLF is a varchar with length 50. It's never null, but it can be empty.
KUNDE_TLF_MOB is a varchar with length 50. It's never null, but it can be empty.

When KONTAKT_MOB is empty, this works fine, the result is correct. However, every time both KONTAKT_TLF and KUNDE_TLF_MOB are empty, the end result is also empty - even though KONTAKT_MOB contains 10 chars.

If I put COALESCE(KONTAKT_MOB,KONTAKT_TLF,KUNDE_TLF_MOB,'tomt'), then "tomt" is never used. It's like it refuses to ever return KONTAKT_MOB.

Any ideas why?

CodePudding user response：

please try something like this:

with data (KONTAKT_MOB,KONTAKT_TLF,KONTAKT_TLF_MOB) as (values
('','X',''),
('','','')
)
SELECT COALESCE(NULLIF(KONTAKT_MOB,''),
                NULLIF(KONTAKT_TLF,''),
                NULLIF(KONTAKT_TLF_MOB,''),
                'tomt')
from   data