PLSQL - 一次外连接翻车的教训

近期写视图时用外连接翻了车，正好还原了曾经犯过的但没有及时记录的错误，现总结本文。

• 需求简述：

1. 写一个采购订单行信息的视图，其中包含一部分说明性弹性域字段
2. 这里我们简化为两个弹性域字段，均为非必需字段，关联独立值集
3. 为了适应更换值集的可能，应用弹性域段分配表灵活取值集
4. 备注：OEBS采购订单行表PO_LINES_ALL，弹性域段信息表FND_DESCR_FLEX_COLUMN_USAGES，独立值集值视图FND_FLEX_VALUES_VL

• 翻车经过：

由于弹性域字段是非必需的，POL表的ATTRIBUTE字段关联FFV时必然要用外连接，FFV的值集ID要从FCU表里取，所以我自然而然的把FCU表也外连接了进来，结果就翻车了

初版代码就像这样，先拿引入一个弹性域字段举例

SELECT pol.po_line_id, ffv2.flex_value_meaning project_no
  FROM po_lines_all                 pol
      ,fnd_descr_flex_column_usages fcu2
      ,fnd_flex_values_vl           ffv2
 WHERE pol.attribute2 = ffv2.flex_value(+)
   AND ffv2.flex_value_set_id = fcu2.flex_value_set_id(+)
   AND fcu2.descriptive_flexfield_name(+) = 'PO_LINES'
   AND fcu2.descriptive_flex_context_code(+) = 'Global Data Elements'
   AND fcu2.application_column_name(+) = 'ATTRIBUTE2';

• 反思：

上面SQL翻车的现象就是，POL.ATTRIBUTE2有值的行大批量的重复了，说实话一开始我都看不出来到底是发生肾么事了

后来从外连接的定义入手，想到了翻车的原因

例如A表和B表外连接，其中A做“主表”，外连接起到的作用是：1、A表所有的行都要被选择出来；2、B表能关联到A表的行，就关联进结果集；3、对于关联不到B表的A表的行，也要被选到，只不过该行B表的字段用NULL填充

在这里，pol.attribute2 = ffv2.flex_value(+)这个外连接条件尚且问题不大，但是FCU表再外连接到FFV表就不妙了：宏观上看，连接的顺序变成了POL与FFV外连接后，又与FCU进行外连接

从微观上，我们举个简单的例子，POL某行ATTRIBUTE2值为60，FFV中有两个值集含有60这个独立值，第一步上POL这一行要和FFV中的这两行都关联到。

再假设这两个值集分别为M和N，M是弹性域引用的，而N不是。我们为了限制到M值集引入了FCU表，但是FCU表是被外连接进来的，所以说不论是M值集还是N值集都将被保留，FCU表起不到任何限制作用

• 改正：

FFV表和FCU表在本例中不是相互独立的关系，事实上我们是想用FCU表的唯一行去限制FFV表的记录范围，所以两表密不可分，应视同一体。我想的办法是两表先写作内嵌视图，再与POL表外连接

SELECT pol.po_line_id
      ,ffv2.flex_value_meaning  project_no
      ,ffv10.flex_value_meaning product_line
  FROM po_lines_all pol
      ,(SELECT ffv.flex_value, ffv.flex_value_meaning
          FROM fnd_descr_flex_column_usages fcu, fnd_flex_values_vl ffv
         WHERE fcu.descriptive_flexfield_name = 'PO_LINES'
           AND fcu.descriptive_flex_context_code = 'Global Data Elements'
           AND fcu.application_column_name = 'ATTRIBUTE2'
           AND fcu.flex_value_set_id = ffv.flex_value_set_id) ffv2
      ,(SELECT ffv.flex_value, ffv.flex_value_meaning
          FROM fnd_descr_flex_column_usages fcu, fnd_flex_values_vl ffv
         WHERE fcu.descriptive_flexfield_name = 'PO_LINES'
           AND fcu.descriptive_flex_context_code = 'Global Data Elements'
           AND fcu.application_column_name = 'ATTRIBUTE10'
           AND fcu.flex_value_set_id = ffv.flex_value_set_id) ffv10
 WHERE pol.attribute2 = ffv2.flex_value(+)
   AND pol.attribute10 = ffv10.flex_value(+);

ANSI版本看起来可能更直观一点

原来翻车的SQL

SELECT pol.po_line_id, ffv2.flex_value_meaning project_no
  FROM po_lines_all pol
  LEFT JOIN fnd_flex_values_vl ffv2
    ON pol.attribute2 = ffv2.flex_value
  LEFT JOIN fnd_descr_flex_column_usages fcu2
    ON ffv2.flex_value_set_id = fcu2.flex_value_set_id
   AND fcu2.descriptive_flexfield_name = 'PO_LINES'
   AND fcu2.descriptive_flex_context_code = 'Global Data Elements'
   AND fcu2.application_column_name = 'ATTRIBUTE2';

更正后的SQL

SELECT pol.po_line_id, ffv2.flex_value_meaning project_no
  FROM po_lines_all pol
  LEFT JOIN (SELECT ffv.flex_value, ffv.flex_value_meaning
               FROM fnd_descr_flex_column_usages fcu
               JOIN fnd_flex_values_vl ffv
                 ON fcu.descriptive_flexfield_name = 'PO_LINES'
                AND fcu.descriptive_flex_context_code =
                    'Global Data Elements'
                AND fcu.application_column_name = 'ATTRIBUTE2'
                AND fcu.flex_value_set_id = ffv.flex_value_set_id) ffv2
    ON pol.attribute2 = ffv2.flex_value
  LEFT JOIN (SELECT ffv.flex_value, ffv.flex_value_meaning
               FROM fnd_descr_flex_column_usages fcu
               JOIN fnd_flex_values_vl ffv
                 ON fcu.descriptive_flexfield_name = 'PO_LINES'
                AND fcu.descriptive_flex_context_code =
                    'Global Data Elements'
                AND fcu.application_column_name = 'ATTRIBUTE10'
                AND fcu.flex_value_set_id = ffv.flex_value_set_id) ffv10
    ON pol.attribute10 = ffv10.flex_value;

• 注意：

原版翻车SQL不能改写成形如下面的版本，因为ORA-01799: 列不能外部联接到子查询

SELECT pol.po_line_id, ffv2.flex_value_meaning project_no
  FROM po_lines_all pol, fnd_flex_values_vl ffv2
 WHERE pol.attribute2 = ffv2.flex_value(+)
   AND ffv2.flex_value_set_id(+) =
       (SELECT fcu2.flex_value_set_id
          FROM fnd_descr_flex_column_usages fcu2
         WHERE fcu2.descriptive_flexfield_name = 'PO_LINES'
           AND fcu2.descriptive_flex_context_code = 'Global Data Elements'
           AND fcu2.application_column_name = 'ATTRIBUTE2');

• 拓展：

相比全局上下文的情况，根据数据表行的上下文动态关联独立值集似乎更值得探讨

沿用之前反思的套路，此时变成POL表与FCU表视同一体了，因为FCU表的“值集ID”列更像是对POL的一个增补，所以FFV应该是与前两者的结合体进行外连接的

ANSI版本

SELECT pol.po_line_id, ffv15.flex_value_meaning project_no
  FROM po_lines_all pol
  JOIN fnd_descr_flex_column_usages fcu15
    ON fcu15.descriptive_flexfield_name = 'PO_LINES'
   AND fcu15.descriptive_flex_context_code = pol.attribute_category
   AND fcu15.application_column_name = 'ATTRIBUTE15'
  LEFT JOIN fnd_flex_values_vl ffv15
    ON ffv15.flex_value = pol.attribute15
   AND ffv15.flex_value_set_id = fcu15.flex_value_set_id;

Oracle SQL版本的写法要注意了，虽然POL表与FCU表绑定一起了，但与FFV进行外连接时分别要用到来自POL的ATTRIBUTE字段和来自FCU的值集ID字段，在12版本以前的数据库中，尚不允许一张表外连接到多张表（ORA-01417），解决方案是先写内嵌式图

-- 12以下版本
SELECT pol.po_line_id, ffv15.flex_value_meaning equi_spec
  FROM (SELECT l.po_line_id, l.attribute15, u.flex_value_set_id
          FROM po_lines_all l, fnd_descr_flex_column_usages u
         WHERE u.descriptive_flexfield_name = 'PO_LINES'
           AND u.descriptive_flex_context_code = l.attribute_category
           AND u.application_column_name = 'ATTRIBUTE15') pol
      ,fnd_flex_values_vl ffv15
 WHERE pol.flex_value_set_id = ffv15.flex_value_set_id(+)
   AND pol.attribute15 = ffv15.flex_value(+);

-- 12及更高版本
SELECT pol.po_line_id, ffv15.flex_value_meaning equi_spec
  FROM po_lines_all                 pol
      ,fnd_descr_flex_column_usages fcu15
      ,fnd_flex_values_vl           ffv15
 WHERE fcu15.descriptive_flexfield_name = 'PO_LINES'
   AND fcu15.descriptive_flex_context_code = pol.attribute_category
   AND fcu15.application_column_name = 'ATTRIBUTE15'
   AND fcu15.flex_value_set_id = ffv15.flex_value_set_id(+)
   AND pol.attribute15 = ffv15.flex_value(+);

SELECT pol.po_line_id, ffv2.flex_value_meaning project_no
  FROM po_lines_all                 pol
      ,fnd_descr_flex_column_usages fcu2
      ,fnd_flex_values_vl           ffv2
 WHERE fcu2.descriptive_flexfield_name = 'PO_LINES'
   AND fcu2.descriptive_flex_context_code = 'Global Data Elements'
   AND fcu2.application_column_name = 'ATTRIBUTE2'
   AND fcu2.flex_value_set_id = ffv2.flex_value_set_id(+)
   AND pol.attribute2 = ffv2.flex_value(+);