problem in query

[ag_201074]

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

[slimdave]

Maybe there's a more efficient way, but here you go ...

Select Decode(q2,0,q1,q2)
From
(Select count(*) q1 from table),
(Select count(*) q2 from table where some_cond)
/

[ag_201074]

Thnx slimdave guess it'll work

[John Spencer]

How about:

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

[WilliamR]

Presumably you could simplify that slightly to (untested):
SELECT CASE (SELECT 1 FROM t WHERE rownum = 1)
WHEN 1 THEN (SELECT count(*) FROM t)
ELSE (SELECT count(*) FROM t1)
END
FROM dual;

[John Spencer]

William:

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

John