Not sure this is the right area, but I'm trying to edit a pre-existing SQL query in HTMLDB (Application Express) in Oracle.
Here's the current query that is returning a list of pages from a wiki log (that's been ingested into a table), telling the number of times it's been edited, the page that's been edited, when it was last edited, and who did it.
---------------
select count(a.page) numedits, a.page, b.last_edit, c.case_id
from wiki_log_data_c hanges a,
(select max(accessed_dt ) last_edit, page from wiki_log_data_c hanges group by page) b,
(select accessed_dt last_edit, page, case_id from wiki_log_data_c hanges) c
where a.page = b.page
and b.page = c.page
and upper(a.page) != upper('start')
group by a.page, b.last_edit, c.last_edit, c.case_id
having b.last_edit = c.last_edit
----------------
I'm still fairly new to SQL, but I'm trying to edit this to include additional information from another table with data on the views, adding the same info but from another table - with the final result showing the page name and then all the edit and view data all in one region.
Here's what I've got so far, but when I try to execute it in Toad, it just hangs, but doesn't seem to stop... Not sure if the fundamental logic is wrong, or if it's a matter of trying to compare to VARCHAR strings together taking FOREVER... any thoughts would be greatly appreciated.
----------------
select a.page, count(a.page) num_edits, b.last_edit, c.case_id, count(d.page) num_views, e.last_view, f.case_id
from wiki_log_data_c hanges a,
(select max(accessed_dt ) last_edit, page from wiki_log_data_c hanges group by page) b,
(select accessed_dt last_edit, page, case_id from wiki_log_data_c hanges) c,
wiki_log_data d,
(select max(accessed_dt ) last_view, page from wiki_log_data group by page) e,
(select accessed_dt last_view, page, case_id from wiki_log_data) f
where a.page = d.page
and b.page = c.page
and e.page = f.page
group by a.page, b.last_edit, c.case_id, c.last_edit, e.page, e.last_view, d.page, f.case_id
having b.last_edit = c.last_edit
---------------------
Thanks!
Roger