Re: Outer Join issue

On 4 Apr 2007 12:21:39 -0700, "Steve" <stevelaba_at_yahoo.com> wrote:

>Hello all, using 9.2 and can't understand why my join is not working.
>When I outer join table addr using mn_sale.contracted_cust_id =
>addr.member_id(+) I get back less rows then if I don't join this
>table.
>
>I believe I should be getting the same amount of rows that the table
>mn_sale returns.
>
>Please advise.
>
>Thanks
>
>Steve
>
>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

Do the following. This will hopefully make clear what is going on. replace count(1) by
contracted_cust_id, member_id, addr.realm_num

Now run the same query with the last predicate like this
-- and addr.realm_num = 1024
Do you see NULL realm_num's ?
You should otherwise the outer join is not necessary. The combination mn_sale.realm_num = 1024 and addr.realm_num = 1024 stinks. You probably have a denormalized design. You need to make sure addr.realm_num can be NULL in your where clause.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA