Login or Register to Ask a Question and Join Our Community


Programming

SQL*PLUS How to display a count of 0

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming SQL*PLUS How to display a count of 0
# 1  
Old 01-21-2015
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:

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

Which I would like to return

Code:
valueA
-------
0

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

Any ideas?

Thanks
# 2  
Old 01-21-2015
This looks good to me with an Oracle database.
Code:
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
# 3  
Old 01-21-2015
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)

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

# 4  
Old 01-21-2015
You can get a count of 0 by including 'ME' in the data set you select from:
Code:
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.
Last edited by cero; 01-21-2015 at 08:47 AM..
These 2 Users Gave Thanks to cero For This Post:
chris01010 rbatte1
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to find the count of IP addresses that belong to different subnets and display the count?

Hi, I have a file with a list of bunch of IP addresses from different VLAN's . I am trying to find the list the number of each vlan occurence in the output Here is how my file looks like 1.1.1.1 1.1.1.2 1.1.1.3 1.1.2.1 1.1.2.2 1.1.3.1 1.1.3.2 1.1.3.3 1.1.3.4 So what I am trying... (2 Replies)
Discussion started by: new2prog
2 Replies

2. Shell Programming and Scripting

Sql query output count

Hi Team, below sql rerturn 20 records, the result set i am going to assign to one variable and it showing count is 1. and i don't use count() in sql query... based on count, i need to fail the script. No_of_step=`echo ${g_count} | wc -l` function gf_count() { g_count=`sqlplus -s... (8 Replies)
Discussion started by: bmk123
8 Replies

3. UNIX for Dummies Questions & Answers

Single UNIX command to display users and to count them

Hello everyone, I am new to Unix and I am stuck with a problem. I need only a single command to display the output of who and then add the total number of users and display at the bottom of that output. Example-: (Expected output) sreyan@debian:~$ <command> sreyan tty7 ... (7 Replies)
Discussion started by: sreyan32
7 Replies

4. Shell Programming and Scripting

Cannot get literal ampersand to display in SQL output

Gurus, Thanks so much for your help, in advance. I'm using ksh and outputting a literal string value to an output file, however, Unix isn't playing by SQL's rules. The ampersand character which I'm trying to disply as a knowledge base link is screwing up the output. Typically, the "&&" is... (1 Reply)
Discussion started by: WhoDatWhoDer
1 Replies

5. UNIX for Advanced & Expert Users

v$sql not display correct sql_text

Hi folks, I am facing one problem with v$sql, i need to store updating sql query in temp table when one trigger get fired on some update sql statement. but with "sql_text" , i am not getting correct update statement in temp table. I am getting sql_text with this cursor statement. select... (0 Replies)
Discussion started by: apskaushik
0 Replies

6. Shell Programming and Scripting

Read File and Display The Count of a particular field

Hi Mates, I require help in the following: I have the following file snmp.txt Wed Mar 2 16:02:39 SGT 2011 Class : mmTrapBladeS origin : 10.0.0.0 hostname : 10.0.0.2 msg : IBM Blade Alert: Calendar Index : 10.0.0.2-IBMBLADE Fri Mar 4 07:10:54 SGT 2011 Class : mmTrapBladeS... (2 Replies)
Discussion started by: dbashyam
2 Replies

7. Shell Programming and Scripting

How to display fields and values in sql+ for Oracle DB

Hello, I'm trying to do a select for an Oracle table but the output gives me only filelds values without fields name as in Informix. Is there anyway to display both in output ? For instance, the output will be : Name Rico Age 30 Position Engineer Thx, (5 Replies)
Discussion started by: rany1
5 Replies

8. Shell Programming and Scripting

How to display and count

Hi there, I'd like to find a way to display a string and count the words in it. supernova:~# echo 'hello world' | tee - | wc Unfortunately, this doesn't work. Any idea? Thanks in advance. Santiago (15 Replies)
Discussion started by: chebarbudo
15 Replies

9. Shell Programming and Scripting

Display the count of files

I am new to shell programming. Can anyone help me out with anyone of these? Display a count of the number of regular files, the number of symbolic links, the number of sub-directories, the number of block-special files, and the number of character-special files in the directory. I don't... (4 Replies)
Discussion started by: wayne1411
4 Replies
Login or Register to Ask a Question