How to display a default value when no match found in a query?

Question

I want to display a default message when there is no data obtained from a query.

For example Let us take a query

select empname from employee where id = 100

If no data matches this search i want to get Unavailable as a result or the required result should display.

So how should i write a SQL query to achieve this.

I am using Oracle 10g.

You really should just return `null` and let your calling application decide how it will behave when encountering `db_null` instead of doing it in your DB. — Seph, Nov 20 '11 at 09:14
@Seph : Thanks for the tip, if i return null in the calling application i have to replace the null with the a default message. So instead of the if i an trying to do it the query itself. — Hariharbalaji, Nov 20 '11 at 09:35
I had already had thought of all these scenario's and then only moved the IF() to SQL as the behavior was be the same in both the places — Hariharbalaji, Nov 20 '11 at 10:14

Erwin Brandstetter · Accepted Answer · 2013-10-16T14:20:45.997

20

SELECT COALESCE((SELECT empname FROM employee WHERE id = 100), 'Unavailable')
FROM   DUAL;

You have to wrap the SELECT into another SELECT or no row will be returned. And where there is no row, there cannot be a value.

edited Oct 16 '13 at 14:20

answered Nov 20 '11 at 08:59

1 Answers1