C4LYXX6P

1select case 
2when count(*) = 0    
3then 'no Entry'   
4else MAX(Member)--all Members should be here 
5END as Member 
6from tableMember 
7where Membergroup = 'testgroup';
8	
9select member from tablemember where Membergroup = 'testgroup'
10union 
11 select  'no Entry' 
12 from dual 
13 where NOT EXISTS ( select member from tablemember where membergroup = 'testgroup')
14;
15	
16select case when max_member is null then 'no entry' else max_member end as member
17  from ( select max(member) as max_member
18           from tablemember
19          where membergroup = 'testgroup'
20                )
21	
22select member
23  from tablemember
24 where membergroup = 'testgroup'
25 union all
26select 'no entry'
27  from dual
28 where not exists ( select 1 from tablemember where membergroup = 'testgroup')
29	
30SELECT nvl(a.member,b.member) member
31   FROM (SELECT member FROM tablemember WHERE membergroup='????') a
32   RIGHT JOIN (SELECT 'no Entry' member FROM dual) b ON 1=1;
33	
34DROP TABLE tablemember;
35CREATE TABLE tablemember AS
36   (
37      SELECT TO_CHAR(level) member
38         , DECODE(mod(level, 5), 0, 'testgroup', 'othergroup') membergroup
39      FROM dual CONNECT BY level <= 50
40   );
41	
42DECLARE C INTEGER;
43
44SELECT COUNT(*) INTO C FROM tableMember WHERE Membergroup = 'testgroup';
45
46IF C > 0
47
48THEN
49
50    SELECT * FROM tableMember;
51
52ELSE
53
54    SELECT 'No results!' FROM tableMember;
55
56END IF;