复杂的左外部加入Oracle，转换为PostgreSQL

Question

我有一条来自Oracle + SAS环境的巨大SQL语句。我得到了大部分，但最让我困惑的是WHERE子句中的Left Outer Joins
/加号。我需要将其转换为Postgres。我可以处理代码的第一部分，这是使我感到困惑的联接。

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(+)

我知道从Oracle转换时如何进行简单的联接，但是，该联接中有多个相同表的实例正在进行联接比较，并且混合了许多不需要联接的条件。那么如何进行联接呢？语句结尾是否需要其他WHERE子句？

谢谢。

Answer 1

试试这个：

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