SQL*PLUS How to display a count of 0

Hi,

I have been frantically googling and checking some sqlplus forums, but can't find the correct syntax.

Basically within sqlplus I want to do a count on a table and if the count is 0 it displays 0 instead of "no rows found".

For eample:

 
select count(*) from tableA where valueA='X';
 

Which I would like to return

valueA
-------
0

if there are no rows on the table where valueA='X'

Any ideas?

Thanks

This looks good to me with an Oracle database.

SQL> set heading off
SQL> select count(*) from dba_tables where owner = 'RBATTE1' ;

         0

SQL> 

I am logged on to the unix server and then a local sqlplus session, if that makes a difference. I'm not sure why it would though.

Robin

OK, thanks. I've ended up asking the wrong question by trying to simplify it too much :0)

So my actual query is a bit more complicated and uses case and group by conditions.

So how can I make it display a count of 0 if no rows are found in this situation

e.g. (another simplified select not including the case)

select owner, table_name, count(*) from all_tables
where owner='ME'
group by owner, table_name;
 
no rows selected
 

You can get a count of 0 by including 'ME' in the data set you select from:

select owner,table_name,count(table_name)
  from (select owner,table_name from all_tables
        union all
        select 'ME',null table_name from dual)
 where owner = 'ME'
 group by owner,table_name
/

ME                                                                            0

But be careful: count(*) counts the number of records. Because there now is a record with owner='ME' you'd get a result of 1. You get 0 because table_name is null in that record.

2 Likes