help with outer join

[JRO061601]

I can't seem to get an outer join working the way I want. Given the following:

1) the 'sample_custs' view will return a sample of ~100 customer IDs.
2) the 'customer_computed_value' table will have all the customer IDs.
3) the 'order_basket' table will only have a subset of the customer IDs.

I want the last statement (after the union) to return all the customer IDs, even if they are not in the 'order_basket' table. This query is only returning the inner join. Reversing the outer join to a 'right outer join' returns a null record. A full outer join returns the null record and the inner join. How can I get the customer IDs in the sample, but not in the order_basket table, to be listed?



with sample_custs as (
select a.customer_id
from customer sample(0.02) a)

select 'CCV',
b.customer_id,
b.c12_gross_amt
from customer_computed_value b, sample_custs c
where b.customer_id = c.customer_id

union

select x.*
from
(
select 'Order Basket',
e.customer_id,
sum(d.total_gross_amt)
from order_basket d, sample_custs e
where d.customer_id = e.customer_id
and d.transaction_dt between to_date('16-SEP-2005', 'DD-MON-YYYY') - 365 and
'16-SEP-2005'
group by e.customer_id) x left outer join sample_custs y on x.customer_id = y.customer_id
;

 

[IanWaterman]

YOu second query should be

select x.type, y.customer_id, x.TGA
from sample_custs y left outer join
(
select 'Order Basket' Type,
e.customer_id,
sum(d.total_gross_amt) TGA
from order_basket d, sample_custs e
where d.customer_id = e.customer_id
and d.transaction_dt between to_date('16-SEP-2005', 'DD-MON-YYYY') - 365 and
'16-SEP-2005'
group by e.customer_id) x
on y.customer_id = x.customer_id
;

 

[JRO061601]

got it to work, thanks!