无法确定“列定义不明确”错误的原因

Question

下面是SQL语句：

    SELECT alloc.oa_id
  FROM    qdod.qtran_owner_allocation alloc
       INNER JOIN
          (SELECT h.oa_id, h.div_ord_no, h.process_queue_id, h.from_ba_no,
                  h.from_ba_suf, h.from_interest_type_cd, h.from_interest_type_cd, h.from_div_ord_grp,
                  h.transfer_percent, h2.original_net_amount, h2.new_net_amount
             FROM    qdod.qtran_fund_transfer_hist h
                  INNER JOIN
                     (SELECT DISTINCT h0.oa_id, h0.original_net_amount, h1.new_net_amount
                        FROM    qdod.qtran_fund_transfer_hist h0
                             INNER JOIN
                                (SELECT h4.oa_id, SUM (h4.new_net_amount) AS new_net_amount
                                   FROM qdod.qtran_fund_transfer_hist h4
                                 GROUP BY h4.oa_id) h1
                             ON h0.oa_id = h1.oa_id
                       WHERE h0.original_net_amount <> h1.new_net_amount AND h0.oa_id >= 100000000) h2
                  ON h.oa_id = h2.oa_id) h3
       ON alloc.oa_id = h3.oa_id;

每一列都定义了它的表。主内连接( alloc表之后的连接)在单独运行时运行良好。你知道为什么这不起作用吗？这是在Oracle 10.2.0.4数据库上执行的(我也在11.2.0.1数据库上尝试过，我认为如果这是Oracle的bug，它会在11.2中解决，但在11.2中也失败了)。

Answer 1

字段在语句中重复，可能与此有关

h.from_interest_type_cd, h.from_interest_type_cd,

Answer 2

您似乎选择了许多您并不真正需要的列，因为您在任何地方都没有使用它们。查询可能简化为：

  SELECT alloc.oa_id
  FROM    qdod.qtran_owner_allocation alloc
       INNER JOIN
          (SELECT h.oa_id
             FROM    qdod.qtran_fund_transfer_hist h
                  INNER JOIN
                     (SELECT DISTINCT h0.oa_id
                        FROM    qdod.qtran_fund_transfer_hist h0
                             INNER JOIN
                                (SELECT h4.oa_id, SUM (h4.new_net_amount) AS new_net_amount
                                   FROM qdod.qtran_fund_transfer_hist h4
                                 GROUP BY h4.oa_id) h1
                             ON h0.oa_id = h1.oa_id
                       WHERE h0.original_net_amount <> h1.new_net_amount AND h0.oa_id >= 100000000) h2
                  ON h.oa_id = h2.oa_id) h3
       ON alloc.oa_id = h3.oa_id;