Getting ORA-00918: column ambiguously defined: running this SQL:(When adding a table)

Question

I'm getting error ORA-00918: column ambiguously defined when running this SQL:(When adding a table ra_interface_lines_all)

SELECT DISTINCT rcta.TRX_NUMBER
              , rcta.trx_date
              , rcta.PRINTING_OPTION
              , rcta.PRINTING_PENDING
              , rcta.CREATED_FROM
              , houf.NAME  Business_Unit
              , xep.NAME   Legal_Entity
              , rctta.NAME Transaction_Type
              , rcta . invoice_currency_code
              , aaa.gl_date
              , rtb.NAME   Payment_Name
              , hca.account_name
FROM   ra_customer_trx_all        rcta
     , hr_organization_units_f_tl houf
     , xle_entity_profiles        xep
     , ra_terms_tl                rtb
     , RA_CUST_TRX_TYPES_ALL      rctta
     , hz_cust_accounts           hca
     , AR_ADJUSTMENTS_ALL         aaa
     , ra_interface_lines_all
WHERE  trx_number           = 3021
   AND rcta.org_id          = houf.organization_id
   AND xep.legal_entity_id  = rcta.legal_entity_id
   AND rcta.SET_OF_BOOKS_ID = aaa.SET_OF_BOOKS_ID 

Answer 1

This errors appears when you have multiples table with same columns name and this column name is used in the statement without alias. So the interpretor cannot determine wich table it should use.

To fix your query, check all columns without alias and prefix them. (trx_number for example)

Answer 2

ra_interface_lines_all table has trx_number column which exist in other table(s), therefore you must reference table name (or alias) to column.

In your case if it's the new table you can change to:

 , ra_interface_lines_all
   WHERE  ra_interface_lines_all.trx_number           = 3021