Re: Outer Join issue

From: Thorsten Kettner <thorsten.kettner_at_web.de>
Date: 5 Apr 2007 00:36:08 -0700
Message-ID: <1175758568.506542.59030@l77g2000hsb.googlegroups.com>

On 4 Apr., 21:21, "Steve" <stevel..._at_yahoo.com> wrote:
> SELECT count(1)
> FROM modn.mn_sale mn_sale ,
> modn.mn_address addr ,
> modn.mn_date_dim dt_dim
> WHERE mn_sale.realm_num = 1024
> AND mn_sale.lifecycle_status = 30
> AND addr.addr_flags = 1
> AND to_date(dt_dim.short_date_ymd,'YYYY-MM-DD')
> BETWEEN to_date('2006-07-01','YYYY-MM-DD')
> AND to_date('2006-12-31','YYYY-MM-DD')
> AND mn_sale.sale_type <> 1
> AND mn_sale.inv_date_id = dt_dim.date_dim_id
> AND mn_sale.contracted_cust_id = addr.member_id(+)
> AND addr.realm_num = 1024

Your outer join is not complete. You compare the customer id as an outer join clause, but then you desire only records of addr_flags = 1 and realm_num = 1024, so every record you outer-joined gets refused and the whole idea of an outer join gets lost. It should be:

    AND addr.realm_num(+) = 1024
    AND addr.addr_flags(+) = 1

This an error that easily happens with the old Oracle specific outer join syntax. With Ansi SQL you get it much more readable:

SELECT count(1)
 FROM modn.mn_sale mn_sale
 JOIN modn.mn_date_dim dt_dim on
(dt_dim.date_dim_id=mn_sale.inv_date_id)  LEFT OUTER JOIN modn.mn_address addr on (addr.member_id=mn_sale.contracted_cust_id and

addr.realm_num=mn_sale.realm_num and addr.addr_flags=1)
  WHERE mn_sale.realm_num       = 1024
    AND mn_sale.lifecycle_status = 30
    AND mn_sale.sale_type                 <>  1
    AND to_date(dt_dim.short_date_ymd,'YYYY-MM-DD') BETWEEN

to_date('2006-07-01','YYYY-MM-DD') AND to_date('2006-12-31','YYYY-MM- DD')

One more thing I'd like to mention: dates are datetimes actually. You don't specify any hour in to_date, so 00:00:00 is assumed. And 2006-12-31 1am is not between 2006-07-01 0am and 2006-12-31 0am. When comparing dates only, you'd better compare them as strings:

AND dt_dim.short_date_ymd BETWEEN '2006-07-01' AND '2006-12-31'

By the way: Why do you store dates as strings in mn_date_dim? And yes, Sybrand Bakker is right, why is there a realm_num in both mn_sale and mn_address when the tables are directly linked by id? Received on Thu Apr 05 2007 - 02:36:08 CDT