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

From: Steven Garcia <sgarcia_at_inktomi.com>
Date: Fri, 29 Mar 2002 13:57:41 -0800
Message-ID: <3ca4e358$1@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

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 Fri Mar 29 2002 - 15:57:41 CST