Date: Fri, 23 Oct 2009 16:56:38 -0700 (PDT)
Hope I can explain this properly:
I'm hoping this can be done in just 1 long SQL statement. We have several queries that gather counts from different tables. Each takes a while and since they are part of the same app, the screen takes a while. So, we want to use a materialized view.
Problem is that each column would need to be a query. The tables are different, though we basically need the same data from them, a date and a count of records on that date. So, each query will return different results, but yet we want 1 materialized view to contain the counts of the 6 tables. For example, here are 2 of the queries:
event_date, count(*) DIVIDENDS
DISTINCT(ticker) ticker, company_name company, div_rate_per_share dividend, announcement_date event_date, announcement_date announcement_date, div_expected_date ex_date, record_date record_date, payment_date payment_date FROM data_holder.expected_dividends
DISTINCT(ticker) ticker, company_name company, div_rate_per_share dividend, div_expected_date event_date, announcement_date announcement_date, div_expected_date ex_date, record_date record_date, payment_date payment_date
And another one:
t.q0_expected_report_date, count(*) earnings FROM
e.m_ticker(+) = m.m_ticker AND m.ticker = t.ticker AND t.late_last_flag = 0 AND t.report_date = (SELECT MAX(a.report_date)FROM
t.ticker = a.ticker AND
a.late_last_flag = 0)
group by t.q0_expected_report_date
order by t.q0_expected_report_date;
So, each column in the view will represent a query of counts from a particular table. Is this possible? It would speed everything up since we have the results already.
I was able to create a view, but not with the proper data. My guess is some combination of ROW_NUMBER or COUNT or something like that.... Received on Fri Oct 23 2009 - 18:56:38 CDT