-
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
-
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
Aniruddha Gupta
-
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
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
|