I'm trying to port the following sql code to PostgreSQL. I can't
figure out how to write the outer joins
create or replace view ec_recommendations_cats_view as
select
recs.*,
nvl(cats.category_id,nvl(subs.category_id,subsubs.category_id)) as
the_category_id,
nvl(cats.category_name,nvl
(subs.category_name,subsubs.category_name)) as the_category_name,
nvl(subs.subcategory_id,subsubs.subcategory_id) as
the_subcategory_id,
nvl(subs.subcategory_name,subsubs.subcategory_name) as
the_subcategory_name,
subsubs.subsubcategory_id as the_subsubcategory_id,
subsubs.subsubcategory_name as the_subsubcategory_name
from
ec_product_recommendations recs,
ec_categories cats,
ec_subcategories_augmented subs,
ec_subsubcategories_augmented subsubs
where
recs.category_id = cats.category_id(+)
and
recs.subcategory_id = subs.subcategory_id(+)
and
recs.subsubcategory_id = subsubs.subcategory_id(+);
At first, I tried this:
from
((ec_product_recommendations recs left join ec_categories cats on
(recs.category_id = cats.category_id))
left join ec_subcategories subs on (recs.subcategory_id =
subs.subcategory_id))
left join ec_subsubcategories subsubs on (recs.subsubcategory_id =
subsubs.subcategory_id)
But that doesn't seem right. How should I create the outer
joins? Thanks.