Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 238
Hariharbalaji Avatar asked Nov 20 '11 08:11

Hariharbalaji


1 Answers

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.

like image 178
Erwin Brandstetter Avatar answered Oct 18 '22 04:10

Erwin Brandstetter