Re: Outer Join issue

Steve schrieb:
> 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
>

Hi Steve,

you have an additional filter "addr.addr_flags = 1", so maybe some records in mn_address with realm_num 1024 have a different addr_flags value.

Thomas

-- 
For answers by personal mail use: thomas.sommerfeld at domain ust-gmbh.de