Can't identify reason for "column ambiguously defined" error

Question

Here is the 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;

Every column has it's table defined. The main inner join (the one after the alloc table) runs fine when ran by itself. Any ideas why this is not working? This is being executed against an Oracle 10.2.0.4 database (I have also tried it against an 11.2.0.1 database thinking if it was an Oracle bug it would be resolved in 11.2, but it failed there as well).

Answer 1

Field duplicated in the statement, might have something to do with it

h.from_interest_type_cd, h.from_interest_type_cd,

Answer 2

You seem to be selecting a lot of columns you don't really need as you're not using them anywhere. The query could probably be simplified to:

  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;