ORA-00918columnambiguouslydefined
11.2.0.1中同樣的一個SQL能正常執行：
select -- 代金券收入
'VOUCHER' AS SUMMODE,
a.billingdate,
a.rdate,
a.storeroomid,
a.accountscode,
g.accountsname,
I.RELATED,--業務內容
CASE WHEN I.RELATED = 'HQ_billcheckout' THEN H.DICTIONARYCODE ELSE NULL END AS PAYMENTCODE,
CASE WHEN I.RELATED = 'HQ_billcheckout' THEN H.DICTIONARYNAME ELSE NULL END AS PAYMENT,--收款方式名稱
CASE WHEN I.RELATED = 'HQ_billcheckout' THEN h.statistic_class ELSE NULL END AS statistic_class, --收款方式分類
nvl(a.debit,0)-nvl(a.credit,0) as tradeMoney,
0 as StandDealTotalm,
0 as DiscDealTotal,
0 as OtheDealTotal
from HQ_accountingbooks a
inner join m_coupon_grant d on a.bid=nvl(d.bid,d.cgid)
inner join HQ_accounts g on a.accountscode=g.accountscode
LEFT JOIN (SELECT A1.* FROM HQ_DATADICTIONARY A1 WHERE A1.DICTIONARYTYPE=1) H ON g.ACCOUNTSCODE=H.ACCOUNTCODE
inner join HQ_billcase_accounts i on d.billsubcase=i.billsubcase and a.accountscode=i.accountscode
where BILLSUBCASE = 5201;
在11.2.0.4中報錯：
ORA-00918: column ambiguously defined
對比了下，兩個數據庫中所有對應的表表結構都一樣，為什麼在11.2.0.1中能正常執行，在11.2.0.4中執行不了？
發現這個SQL中有2個表中都有BILLSUBCASE這一列：
select BILLSUBCASE from HQ_accountingbooks;
select BILLSUBCASE from m_coupon_grant;
於是指定a.BILLSUBCASE = 5201;即解決。
看來11.2.0.4中對SQL的語法要求更嚴格。
