Hi All,
I am have a little difficulty in figuring this one out. I have the following
2 tables:
PAGES
PAGE_ID
OWNER
LOG
---
PAGE_ID
DATE_LOGGED
From these tables I need to generate something like the following for a
given days log activities:
PAGE_ID COUNT(*)
---------- -----------
899 98
23 76
432 8888
0 78887
But there are some complexities. All pages without an OWNER must be set to 0
and those with an OWNER are set to their PAGE_ID. I can do this with a
DECODE easily enough. Thus far I can achieve with a sub-query. However, I
need to get a NULL or zero count for those rows that have not had a hit on a
specified day. This is where I am stumbling. It looks like a need an outer
join and I have had some success doing this but when I limit the results to
a single day this stops working. What I have so far is as below. If anyone
has any ideas I would REALLY appreciate it.
select
page_id, count(*)
from
(select decode(owner, NULL, p.page_id, 0) as page_id
from pages p, log l where p.page_id = l.page_id (+) and
to_char(l.date_logged, 'DD-MON-YYYY') = '01-FEB-2002')
group by
page_id
Matt.
Received on Thu Jan 31 2002 - 22:48:51 CST
