Unable to determine the cause of the erroneous column definition

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; 

Each column has a specific table. The main inner join (one after the distribution table) works fine at startup. Any ideas why this is not working? This is done in relation to the Oracle database 10.2.0.4 (I also tried it against the database 11.2.0.1, if it was an Oracle error, it will be resolved in 11.2, but it also failed).

Answer 1

A field duplicated in an instruction may have something to do with it.

 h.from_interest_type_cd, h.from_interest_type_cd, 

Answer 2

It seems that you are choosing a lot of columns that you really do not need, since you are not using them anywhere. Perhaps the query can be simplified:

  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;