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

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)
/

---

Thnx slimdave guess it'll work

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

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;

---

William:

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

John