Re: Help with SQL query (and outer joins...)

This is exactly what I came up with later in the evening! I have four tables that I must do this across...so I did a union across the four tables.

The next thing I need to know is whether the performance of this query is just totally crappy. I guess I could run it through tkprof or set autotrace on.

Does this seem like a totally inefficient query?

crappygolucky_at_hotmail.com (crappy) wrote in message news:<ce31c410.0203300030.663438ce_at_posting.google.com>...
> select logday, sum(totalcount), sum(revenue), sum(something_else)
> from (
> select logday, totalcount, 0 revenue, 0 something_else from view1
> union
> select logday, 0, revenue, 0 from view2
> union
> select 0, 0, something_else from view3
> )
> group by logday
>
>
> "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 - 11:14:37 CST