Returning 0 instead of "no rows selected"
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Returning 0 instead of "no rows selected"

  1. #1
    Join Date
    Jul 2001
    Posts
    59
    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

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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;

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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]

  4. #4
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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
  •  



Click Here to Expand Forum to Full Width