Complex left outer joins in Oracle, converting to PostgreSQL
I have this huge SQL statement from Oracle + SAS environment. I get most of this, but I'm most confused by the Left Outer / Plus Signs in the WHERE clause. I need to convert this to Postgres. I can handle the first piece of code, this brings me together.
SELECT
--A bunch of columns from several tables
FROM prd_acct_cmp_grp pacg,
product_acct pa,
customer_acct ca,
(SELECT DISTINCT member_id, group_id
FROM group_members
WHERE group_id IN (33158, 27156, 35376, 36217)) gm,
prd_acct_acct_cmp pac,
pacg_usage pu,
sales_hierarchy sh,
sales_region sr
WHERE pacg.component_group_cd = 'AN'
AND pacg.component_grp_val IN (%s) --string that is added in later
AND pacg.product_account_id = pa.product_account_id
AND pa.customer_acct_id = ca.customer_acct_id
AND ca.customer_acct_id = gm.member_id(+)
AND pacg.product_account_id = pac.product_account_id
AND pacg.occurencce_number = pac.occurence_number
AND pac.prcmp_code = 'USAGE'
AND pacg.component_group_cd = pu.component_group_cd(+)
AND pacg.component_grp_val = pu.component_grp_val(+)
AND ca.primary_sales_rep = sh.sales_rep_id(+)
AND sh.region_cd = sr.sales_region_code(+)
I know how to do simple joins when converting from Oracle however this one has multiple instances of the same tables being compared for joins mixed with many conditions that don't need to be linked. So how would the join be done? And do I need an additional WHERE clause at the end of the statement?
Thank.
+3
source to share
1 answer
Try the following:
SELECT
--A bunch of columns from several tables
FROM prd_acct_cmp_grp pacg
JOIN product_acct pa
ON pacg.product_account_id = pa.product_account_id
JOIN customer_acct ca
ON pa.customer_acct_id = ca.customer_acct_id
JOIN prd_acct_acct_cmp pac
ON pacg.product_account_id = pac.product_account_id
AND pacg.occurencce_number = pac.occurence_number
AND pac.prcmp_code = 'USAGE'
LEFT JOIN (SELECT DISTINCT member_id, group_id
FROM group_members
WHERE group_id IN (33158, 27156, 35376, 36217)) gm
ON ca.customer_acct_id = gm.member_id
LEFT JOIN sales_hierarchy sh
ON ca.primary_sales_rep = sh.sales_rep_id
LEFT JOIN sales_region sr
ON sh.region_cd = sr.sales_region_code
LEFT JOIN pacg_usage pu
ON pacg.component_group_cd = pu.component_group_cd
AND pacg.component_grp_val = pu.component_grp_val
WHERE pacg.component_group_cd = 'AN'
AND pacg.component_grp_val IN (%s) --string that is added in later
+4
source to share