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.

what is the desired output of the query? make an exaple pls. — schurik
– schurik, Commented Aug 17, 2012 at 10:23
You say: "When he has results, post them". Do these results have more than one column? If so, it is impossible what you are asking. A query always has a definite number of columns it returns. Those can't change based on some conditions in the query itself. — Daniel Hilgarth
– Daniel Hilgarth, Commented Aug 17, 2012 at 10:30

schurik · Accepted Answer · 2012-08-17 10:40:19Z

6

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

edited Aug 17, 2012 at 10:40

answered Aug 17, 2012 at 10:33

schurik

7,9582 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ben · Accepted Answer · 2012-08-17 10:26:29Z

4

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

answered Aug 17, 2012 at 10:26

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Comments

Leigh Riffel · Accepted Answer · 2012-08-17 12:50:50Z

1

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

answered Aug 17, 2012 at 12:50

Leigh Riffel

6,6813 gold badges38 silver badges48 bronze badges

Comments

Son of Father · Accepted Answer · 2013-03-21 05:32:47Z

0

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.

edited Mar 21, 2013 at 5:32

answered Mar 21, 2013 at 5:25

Son of Father

1118 bronze badges

Comments

Ben · Accepted Answer · 2012-08-17 12:51:58Z

-2

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;

edited Aug 17, 2012 at 12:51

Ben

53.1k36 gold badges133 silver badges156 bronze badges

answered Aug 17, 2012 at 11:23

Nicholas

2471 silver badge2 bronze badges

1 Comment

Ben Over a year ago

Hi, you can use the code formatting by highlighting the relevant portion of your code and clicking {}. The OP specifically asked for SQL not PL/SQL. Furthermore your code will not compile or display anything. It won't return anything to the client. The second part of your code was not Oracle syntax.

Collectives™ on Stack Overflow

SQL Oracle: Replace an empty result with word

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related