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







All Articles