SQL Oracle: Replace an empty result with word

Question

I'm working on this problem for several days. I have a oracle database. The problem must be resolved in one query. No Function, Pocedure, ... I want to make a select. When he has results, post them. Else there should be "empty result".

select case 
when count(*) = 0    
then 'no Entry'   
else MAX(Member)--all Members should be here 
END as Member 
from tableMember 
where Membergroup = 'testgroup'; 

The problem is that Oracle wants an Agregat function by the else. So I only get one value if the result is not "no entry". I need all values.

Everybody who can help me is welcome and makes me happy.

Answer 1

not sure what do you try to achieve, perhaps this

 select member from tablemember where Membergroup = 'testgroup'
union 
 select  'no Entry' 
 from dual 
 where NOT EXISTS ( select member from tablemember where membergroup = 'testgroup')
;

Answer 2

There's no need for two aggregate queries, you just need to check whether max(member) is null. I'd do it this way to make it clear what's going on.

select case when max_member is null then 'no entry' else max_member end as member
  from ( select max(member) as max_member
           from tablemember
          where membergroup = 'testgroup'
                )

If, however, you want to return all members you can do something like the following:

select member
  from tablemember
 where membergroup = 'testgroup'
 union all
select 'no entry'
  from dual
 where not exists ( select 1 from tablemember where membergroup = 'testgroup')