Search code examples
sqloracle-databasecoalesce

Oracle Coalesce returns blank

I have following query which uses coalesce to return the id of a calendar with a specific code 

 SELECT COALESCE(SD_CALENDAR.ID,0) FROM SD_CALENDAR WHERE SD_CALENDAR.CODE = 'BOER';

But when I run this I get a blank column as result, instead of 0. What do I need to change to make my query work?

Solution

  • You said that no rows in your table match your query, so you are trying to return 0 when there is no match, rather than returning no data at all.

    If NAME is unique then you could use an aggregate to achieve this:

    SELECT COALESCE(MAX(SD_CALENDAR.ID),0) FROM SD_CALENDAR WHERE SD_CALENDAR.CODE = 'BOER';

    The MAX() will always return one row; if there is a match it will be the single ID anyway, and if there isn't it will be null - which you can then coalesce to zero.

    If NAME isn't unique and you expect multiple values back then you can use a union to provide the zero value when there is no match:

    SELECT COALESCE(SD_CALENDAR.ID,0) FROM SD_CALENDAR WHERE SD_CALENDAR.CODE = 'BOER'
UNION ALL
SELECT 0 FROM DUAL WHERE NOT EXISTS (
  SELECT COALESCE(SD_CALENDAR.ID,0) FROM SD_CALENDAR WHERE SD_CALENDAR.CODE = 'BOER'
);

    Depending on what you're doing, it might be better/easier to let your application handle a no-data-found result and substitute a zero itself.