Returning 0 instead of "no rows selected" in oracle

[shagymoe]

What I want to do is write the select statement to return 0 instead of "no rows selected". I was thinking that I could do this with the NVL function, but that doesn't work.

Can anyone enlighten me? I'm sure this is relatively simple, I'm just having mental block.

Thanks,

Shagy

 

[angiole]

You might try the following:

Select count(*)
from   (
       enter entire query here
       )

Cheers

AA 8~)

 

[karluk]

Actually, this isn't simple at all. As far as I know, there is no way to do it.

You are probably referring to the "no rows selected" message generated in an Oracle SQL*Plus session when no rows are returned from your query. It's easy to suppress this message completely. The command to do this is

set feedback off

Unfortunately, that's not what you want. You want to see the message, but change the text. That's difficult because it looks as if the message is hard-coded in a SQL*Plus message file. On my Windows NT workstation with Oracle 8.1.7 client installed, I found the "no rows selected" text in the file {oracle home}\sqlplus\mesg\sp2us.msb

You might think that it would be possible to manually edit the sp2us.msb file, but it didn't work for me. I tried editing it in Notepad, but when I saved it and tried to open SQL*Plus, I got a "file not found" error. Apparently my editing had messed up the file enough that SQL*Plus no longer recognised it. Fortunately I had saved an unedited copy of the file, so I was able to restore it.

All in all, I would say that you should give up and accept the message text that SQL*Plus is generating. Trying to change the message just doesn't seem worth the effort.

 

[dickiebird]

what about .....
if @rowcount = 0 ......??
;-) Dickie Bird
db@dickiebird.freeserve.co.uk

 

[MattMcGinnis]

Thanks for the suggestions. I'm writing the script in perl, and once I changed the query from one "group by" query into three separate queries, it returned 0. Yeah, yeah, I know its slower and not the best solution, but this thing could run all day if it wanted to and no one would care as long as it did complete once per day. The script runs 95 queries and writes the info to an excel file, so there is some formatting code and insertion code too. I takes about 12 minutes to run though. This is on a table that normally has about 1 million rows. I'm not sure if that is good performance or not, but it works acceptably and saves us an immense amount of time. A normal report doesn't quite do the trick because of the presentation and also because adjacent cells have formulas and also, graphs are generated.

MattMcGinnis