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

sql oracle coalesce
Hariharbalaji picture Hariharbalaji · Nov 20, 2011 · Viewed 13.6k times · Source

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.

Answer

Erwin Brandstetter picture Erwin Brandstetter · Nov 20, 2011 
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.

Related questions

Oracle Differences between NVL and Coalesce

Are there non obvious differences between NVL and Coalesce in Oracle? The obvious differences are that coalesce will return the first non null item in its parameter list whereas nvl only takes two parameters and returns the first if it …

Read More
Using NVL for multiple columns - Oracle SQL

Good morning my beloved sql wizards and sorcerers, I am wanting to substitute on 3 columns of data across 3 tables. Currently I am using the NVL function, however that is restricted to two columns. See below for an example: SELECT ccc.…

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More