Now Daniel how could you ask such a question!
Jim
"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3CA4E37A.AD9C26D3_at_exesolutions.com...
> Is this work from a class?
>
> Daniel Morgan
>
>
>
> Steven Garcia wrote:
>
> > I actually think a better example would be the following. I have two
> > tables...
> >
> > Table 1: Table 2:
> > Logday ID PageHits Logday ID UserHits
> > -------- ---- ---------- -------- ---- ---------
> > 20-02 400 10 20-02 400 5
> > 20-02 500 5 20-02 600 5
> > 21-02 400 5 21-02 500 10
> > 21-02 300 15 21-02 300 10
> >
> > I want to create a view that contains the "union" of both these
tables..so
> > the resultset would look like
> >
> > Logday ID PageHits UserHits
> > ----------- ---- ---------- ----------
> > 20-02 400 10 5
> > 20-02 500 5 0
> > 20-02 600 0 5
> > 21-02 400 5 0
> > 21-02 500 0 10
> > 21-02 300 15 10
> >
> > Notice in the columns where no data is present in either one of the
parent
> > tables, a "0" is supplied. A few things...
> >
> > 1) Table 1 and Table 2 are guaranteed to contain complete rows (not
"20-02
> > 400 0").
> > 2) It's OK for the resulting table to have rows where there is 0 in
both
> > the PageHits and UserHits column, or if the
> > resulting table doesn't contain those rows.
> >
> > I'm having trouble formulating a view (sql query) for this. Any help?
> >
> > Thanks, Steve
> >
> > "Steven Garcia" <sgarcia_at_inktomi.com> wrote in message
> > news:3ca4e358$1_at_digink...
> > > I'm having trouble with a view I'm developing. For the simplified
> > > version...I have two views that look like:
> > >
> > > View 1:
> > > Logday (date, 'ddd') totalcount
> > > --------------------- -----------
> > > 20-FEB-2002 10
> > > 21-FEB-2002 20
> > >
> > > View 2:
> > > Logday (date, 'ddd') revenue
> > > --------------------- ----------
> > > 21-FEB-2002 5.00
> > > 22-FEB-2002 8.00
> > >
> > > I want to create an uber view from both of these views that it will
look
> > > like
> > > Logday (date, 'ddd') totalcount revenue
> > > --------------------- ---------- --------
> > > 20-FEB-2002 10 0
> > > 21-FEB-2002 20 5.00
> > > 22-FEB-2002 0 8.00
> > >
> > > I know this is a perfect candidate for an outer join. The real
example
> > I'm
> > > trying to do is more complicated because there are 4 views (each with
a
> > > logday column) and I want to create the uber view that has a column
from
> > > each of the 4 views, along with some other data that will exist across
all
> > > four views (so there won't be a need to do a outer-join on those
columns.)
> > >
> > > How do I construct such a query? I'm trying to solve my problem in
such a
> > > way that my uber view will look like
> > > the following...
> > >
> > > Logday col1 col2 col3
> > > col4
> >
------------------ ---------- ---------- ---------- ----------
> > --
> > > 20-FEB-2002 0 1 15
> > 0
> > > 21-FEB-2002 20 10 0
23
> > > 22-FEB-2002 14 0 14
0
> > > 23-FEB-2002 0 4 0
> > > 34
> > >
> > > where I have 0 in the columns where the data doesn't exist from my
> > "feeder"
> > > views.
> > >
> > > We are currently solving this problem by having a job on a daily basis
do
> > > this population in a table, but our business requirements have changed
> > such
> > > that the current implementation is not suitable. I'm a little worried
> > about
> > > performance of this view but these reporting tables are not access
often
> > in
> > > our application (so a performance hit is something that I will
accept.)
> > >
> > > Thanks for your help,
> > > Steve Garcia
> > >
> > >
>
Received on Sat Mar 30 2002 - 00:16:35 CST
