Returning 0 instead of "no rows selected"

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

Put your select statement inside PL/SQL, test for how many rows are returned, and use dbms_output.put_line('0') when no rows are returned.

DECLARE
v_count number;
BEGIN
select count(whatever) into v_count
from some_table
where some_condition = other_value;

IF v_count = 0 THEN
dbms_output.put_line(v_count);
ELSE
dbms_output.put_line(v_count||' rows returned');
END IF;
EXCEPTION
WHEN OTHERS THEN
shoot_yourself;
END;

Quote:

---

Originally posted by 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.
Shagy

---

do u sure that u need only one field in query?
or
do u need fact are some rows in table or not?
there are two different questions with differemt decisions.

if u need second case than simple, but with bad perfomance select:

select 0 from dual where not exists (select f from tbl where ...)
union
select 1 from dual where exists (select f from tbl where ...);

------------------------------------------------------------------------------------
If u have first case then
select 0 from dual where not exists (select f from tbl where ...)
union
select f from tbl where ...;

but u have to sure that no one row in ur table don't have 0 as value of field f.
otherwise if query return 0 u will not know : this is value of field f or "row not found".



[Edited by Shestakov on 09-27-2002 at 01:45 PM]

Are you trying to run in an SQL script or will this be embedded in another language (C, C++, VB, whatever)?

I believe return code 1401 is no data found. It can be accessed using SQLCA.SQLCODE.