CODEWITHSUNDEEP
sqloracle-databaseselectcase
John Smithv1
John Smithv1

Reputation: 703

SQL Oracle: Replace an empty result with word

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.

Upvotes: 3

Views: 13709

Answers (5)

Son of Father
Son of Father

Reputation: 111

You can use some aggregate functions and NVL for achieve you goal:

SELECT MIN('VALUE 1') AS p1, MIN('VALUE 2') AS p2 FROM DUAL WHERE 1=0

result of this query is: NULL, NULL

next, replace empty values by desired strings:

SELECT 
    NVL(MIN('1'), 'empty  value 1') AS p1, 
    NVL(MIN('STRING VALUE'), 'empty value 2')  AS p2,
    NVL(MIN((select 'subquery result' from dual)), 'empty subquery result') as p3
FROM 
    DUAL 
WHERE 
    1=0

But, you can't mix numbers and strings in fields.

Upvotes: 0

Nicholas
Nicholas

Reputation: 247

Try this:

DECLARE C INTEGER;

SELECT COUNT(*) INTO C FROM tableMember WHERE Membergroup = 'testgroup';

IF C > 0

THEN

    SELECT * FROM tableMember;

ELSE

    SELECT 'No results!' FROM tableMember;

END IF;

Upvotes: -2

Leigh Riffel
Leigh Riffel

Reputation: 6641

If you RIGHT JOIN your query with a query for the empty set you will always get one row and will get all the rows if your query returns data. This is less expensive (faster) than a UNION or UNION ALL with a NOT EXISTS because it does not require multiple scans of the data.

SELECT nvl(a.member,b.member) member
   FROM (SELECT member FROM tablemember WHERE membergroup='????') a
   RIGHT JOIN (SELECT 'no Entry' member FROM dual) b ON 1=1;

Test Environment:

DROP TABLE tablemember;
CREATE TABLE tablemember AS
   (
      SELECT TO_CHAR(level) member
         , DECODE(mod(level, 5), 0, 'testgroup', 'othergroup') membergroup
      FROM dual CONNECT BY level <= 50
   );

Upvotes: 1

schurik
schurik

Reputation: 7928

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')
;

Upvotes: 6

Ben
Ben

Reputation: 52853

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')

Upvotes: 4

Related Questions