problem in query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: problem in query

  1. #1
    Join Date
    Jan 2001
    Posts
    50

    problem in query

    Hi,
    We are facing some problems can anyone help!!!!
    I need a query which is based on the following logic

    Select count(*) from table where some_cond1; ----(1)
    if COUNT(*) = 0 then -----------------------------(2)
    SELECT COUNT(*) from table;
    else ---------------------------------------------(3)
    SELECT count(*) from table where some_cond1;

    end if;

    i.e in case no rows are existing in the table as specified in (1) we do not need to attach any cond for the query otherwise if rows are existing (3) the query we need to use a where clause. Please note we cannot use a procedure.
    regards
    Aniruddha
    Aniruddha Gupta

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Maybe there's a more efficient way, but here you go ...
    Code:
    Select Decode(q2,0,q1,q2)
    From
    (Select count(*) q1 from table),
    (Select count(*) q2 from table where some_cond)
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2001
    Posts
    50
    Thnx slimdave guess it'll work
    Aniruddha Gupta

  4. #4
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    How about:

    Code:
    SELECT CASE WHEN NVL((SELECT 1 FROM t WHERE rownum = 1), 0) = 1 THEN (SELECT count(*) FROM t)
                ELSE (SELECT count(*) FROM t1) END
    FROM dual
    Which should only count(*) one of the tables.

    TTFN
    John

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Presumably you could simplify that slightly to (untested):
    Code:
    SELECT CASE (SELECT 1 FROM t WHERE rownum = 1)
               WHEN 1 THEN (SELECT count(*) FROM t)
               ELSE (SELECT count(*) FROM t1)
           END
    FROM   dual;

  6. #6
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    William:

    Indeed you could (at least in 9.2) where I stil had t and t1 when I tested mine.

    John

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