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.

Solution

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.