How to diplay a message when no records found in a query

If there are no records in a query result it will display nothing.
If client says that a message should be displayed if there are no records with in the query result ,
We can achive it easily in BI Publisher. But if you want to do it in a query it self.

In the below example there are no records with a criteria deptno=50.

So you will get a result with  'no datafound'.


With X as           
(select deptno,'' dummy from emp where deptno=50)
select deptno,dummy from X
union
select NULL,'no datafound' from dual where not exists(select * from X)