How to return default value when no rows return from table

Question

When the table ERRORMESSAGE contains no rows that satisfy the "WHERE" condition, IFNULL fails and I get null, not the default value provided in the IFNULL clause.

select IFNULL(errorCode , 'NOERRORCODE') as "errorCode", 
    IFNULL(errorText , 'NOERROR') as "errorText" from 
    (select errorcode, 
        IFNULL((select errorText from STATIC_ERRORCODES a 
        where a.errorcode = b.errorcode), '') as errorText 
     from ERRORMESSAGE b  where b.route_id = 'IPN4' 
     order by b.row_index)

When there are no rows in ERRORMESSAGE with route_id = 'IPN4', the output should be:

errorCode = NOERRORCODE
errorText = NOERROR

But I get null/empty values. How can I resolve this?

Answer 1

You could use NOT EXISTS and UNION ALL

SELECT errorCode, errorText
FROM ERRORMESSAGE WHERE <condition>
UNION ALL
SELECT 'NOERRORCODE', 'NOERROR'
FROM ERRORMESSAGE
WHERE NOT EXISTS (SELECT * FROM ERRORMESSAGE WHERE <condition>)

You need to replace <condition> with actual criteria. So, when there is are row exists, then SELECT after UNION ALL will get executed, which will show static row.

Please note: I have never used HSQLDB, so I wasn't sure how to show static rows, but I used as explained here