Hi,

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:

SELECT
event_date, count(*) DIVIDENDS
FROM
(
SELECT
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
UNION
SELECT
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
FROM data_holder.expected_dividends
UNION
.
.
.
And another one:

SELECT
t.q0_expected_report_date, count(*) earnings
FROM
master_table m,
estimates e,
data_holder.eps_reports_ex5 t
WHERE
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
data_holder.eps_reports_ex5 a
WHERE
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....

The Magnet schreef:



Hi,

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:

SELECT
   event_date, count(*) DIVIDENDS
FROM
  (
   SELECT
      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
 UNION
   SELECT
      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
   FROM data_holder.expected_dividends
 UNION
.
.
.
And another one:

SELECT
   t.q0_expected_report_date, count(*) earnings
FROM
   master_table m,
   estimates e,
   data_holder.eps_reports_ex5 t
WHERE
   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
   data_holder.eps_reports_ex5 a
WHERE
   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....

Yes you can. Create your different queries per result column in the
materialized view like

select max(first_column), max(second_column), max(third_column)
from
select first_column,0,0
union
select 0, second_column, 0
union
select 0, 0, third_column

The unions create a view with three (or n) rows, and you condense it by
selecting with max. You get a result with one row in your materialized
view. You could even consider using a table, and mark the row with a
calculated date column, so you could even keep your history in the table!

(I even think there is a function to return the first non-null column
you could use in stead of the max, and then you could use null in stead
of 0)

You could get your results in *different* rows (if you would want that)
by using a marker column in your query parts:

select 'count ticker' marker, <your query here
union
select 'count earnings' marker, <your query here
union
select 'count value3' marker, <your query here

which would give you n rows with a column telling you which count is in
that row.

Shakespeare

Hi,

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: