如何在甲骨文中修复'column ambiguously defined'加入

Question

以下是我的代码快照。 我不知道为什么我会遇到这个问题。 请协助我。

select cp.AMOUNT from customer_payee_transactions cp
LEFT OUTER JOIN merchant_transactions mt ON (cp.merchant_transaction_id = 
mt.merchant_transaction_id)
WHERE AMOUNT > 500 AND 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');

请帮我解决这个问题。谢谢。

Answer 1

您需要

在AMOUNT和MODIFIED_DATETIME列之前定义cp，因为您已经为表定义了别名

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');

Answer 2

您需要表别名.列名

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');

在此列MODIFIED_DATETIME之前还要放置表别名