复杂的左外部加入Oracle，转换为PostgreSQL
技术问答
55 人阅读
|
0 人回复
|
2023-09-13
|
|
我有一个来自Oracle SAS环境的巨大SQL语句。我得到了大部分，但最让我困惑的是WHERE子句中的Left Outer Joins
. J2 t4 Y! q: H7 {: s" \/加号。我需要把它转换成Postgres。我可以处理代码的第一部分，这是使我感到困惑的联接。3 L% q. l0 p! c. u0 N5 @5 w0 P
SELECT--A bunch of columns from several tablesFROM prd_acct_cmp_grp pacg, product_acct pa, customer_acct ca, (SELECT DISTINCT member_id,group_id FROM group_members WHERE group_id IN gm, prd_acct_acct_cmp pac, pacg_usage pu, sales_hierarchy sh, sales_region srWHERE 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子句？
& y( l; I# S0 @: v谢谢。
$ J" X( \& o5 |: \% i" E 9 r$ L! g1 t7 o% E- N1 p
解决方案:
- }3 Q+ D+ [0 M" [) V+ S7 ?; | 试试这个：' P: X3 b7 \7 O. [. f
SELECT--A bunch of columns from several tablesFROM prd_acct_cmp_grp pacgJOIN product_acct pa ON pacg.product_account_id = pa.product_account_idJOIN customer_acct ca ON pa.customer_acct_id = ca.customer_acct_idJOIN 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 gm ON ca.customer_acct_id = gm.member_idLEFT JOIN sales_hierarchy sh ON ca.primary_sales_rep = sh.sales_rep_idLEFT JOIN sales_region sr ON sh.region_cd = sr.sales_region_codeLEFT JOIN pacg_usage pu ON pacg.component_group_cd = pu.component_group_cd AND pacg.component_grp_val = pu.component_grp_valWHERE pacg.component_group_cd = 'AN' AND pacg.component_grp_val IN (%s) --string that is added in later |
|
|
|
|
|
|
|
|
|
