如何解决Oracle Join中的‘column ambiguously defined’错误

Hey there! Let's get that annoying error sorted out. The problem here is super straightforward: when you're joining multiple tables, if two tables share column names (like AMOUNT and MODIFIED_DATETIME in your case), Oracle can't tell which one you're referring to in the WHERE clause unless you explicitly specify it.

Looking at your query, you correctly used the alias cp for customer_payee_transactions in the SELECT clause (cp.AMOUNT), but but you forgot to add that same alias (or the mt alias for merchant_transactions) to the AMOUNT and MODIFIED_DATETIME columns in the WHERE clause. Since both tables have those columns, Oracle throws the "column ambiguously defined" error because it doesn't know which table's data you want to filter on.

Here's the corrected query: #

I'm assuming you want to filter using the columns from customer_payee_transactions (since that's where you're selecting AMOUNT from). If you meant to use columns from merchant_transactions instead, just swap cp with mt in the WHERE clause:

SELECT cp.AMOUNT 
FROM customer_payee_transactions cp 
LEFT OUTER JOIN merchant_transactions mt 
    ON (cp.merchant_transaction_id = mt.merchant_transaction_id) 
WHERE cp.AMOUNT > 500 
  AND cp.MODIFIED_DATETIME BETWEEN TO_DATE('27/01/2019 22:00:00', 'DD/MM/YYYY hh24:mi:ss') 
                               AND TO_DATE('27/01/2019 23:00:00', 'DD/MM/YYYY hh24:mi:ss');

A quick best practice tip: #

Get in the habit of prefixing all column names with their table aliases when working with joins. This doesn't just prevent errors like this—it also makes your code way easier to read for anyone else (or future you!) trying to figure out where each column comes from.

内容的提问来源于stack exchange，提问作者L.Anush