-
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;
-
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.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|