Search code examples
sqlselectdb2coalesce

COALESCE function returns no rows

I have a problem with COALESCE function in DB2.

My statement:

SELECT COALESCE(ATTR,'NA') FROM TABLE WHERE %condition%

If ATTR is found based on condition then SELECT returns value of ATTR, but if ATTR is not found then I would expect SELECT returns "NA". However, it returns no rows.

Thanks for help.

Solution

  • To magically create a line:

    SELECT DISTINCT 'NA' AS ATTR FROM TABLE WHERE NOT EXISTS(SELECT 1 FROM TABLE WHERE %condition%)
UNION
SELECT ATTR FROM TABLE WHERE %condition%;

    Explain: Select "NA" if the subquery returns no rows and union it with the subquery itself. Either one of the unioned sets is empty so it gives what you want.