Complex Left Outer Joins in Oracle, converting to PostgreSQL

Question

I have this enormous SQL statement, from an Oracle+SAS environment. I get most of it, but what is confusing me most are the Left Outer Joins/plus signs in the WHERE clause. I need to convert this to Postgres. I can handle the first part of the code, it's the joins that confuse me.

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 in with many conditions that don't need to be joined. So how would the joins be done? And would I need an additional WHERE clause at the end of the statement?

Thanks.

Inner join is your friend. FROM prd_acct_cmp_grp pacg inner join product_acct pa ON pacg.product_account_id = pa.product_account_id. Ect... — Chuck
– Chuck, Commented Jun 30, 2015 at 18:08
@Chuck: there are some left joins in there. That's what makes it more complicated. — sstan
– sstan, Commented Jun 30, 2015 at 18:09

sstan · Accepted Answer · 2015-06-30 18:15:41Z

5

Try this:

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

answered Jun 30, 2015 at 18:15

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

B J Over a year ago

I'll try it out! Unfortunately won't be able to test it until quite a bit later, but I will see if it makes sense syntactically.

Craig Ringer Over a year ago

@BJ The logic here is to convert each FROM table1, table2 WHERE table1.x = table2.y into FROM table1 INNER JOIN table2 ON (table1.x = table2.y). If the WHERE term has a (+), use a LEFT OUTER JOIN with the (+)'d table on the left hand side. I think. I forgot Oracle had that bizarre syntax.

sstan Over a year ago

@Craig: Believe it or not, there used to be a time when I only knew how to do left joins with this bizarre syntax. I actually believed that the ANSI join syntax was ugly and hard to understand. Clearly, something was very wrong with me at the time.

Craig Ringer Over a year ago

@sstan Just goes to show that what you're used to counts for a lot.

Drumbeg Over a year ago

Getting the die hard Oracle guys I work with to write an ANSI join? I've more chance of getting my two year old daughter to learn PL/SQL.

Collectives™ on Stack Overflow

Complex Left Outer Joins in Oracle, converting to PostgreSQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related