ORA-00918 column ambiguously defined, ora00918
In 11.2.0.1, the same SQL statement can be executed normally:
Select -- coupon income
'Voer er' as summode,
A. billingdate,
A. rdate,
A. storeroomid,
A. accountscode,
G. accountsname,
I. RELATED, -- business content
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, -- receipt method name
Case when I. RELATED = 'hq _ billcheckout 'THEN h. statistic_class ELSE NULL END AS statistic_class, -- collection method category
Nvl (a. debit, 0)-nvl (a. credit, 0) as tradeMoney,
0 as StandDealTotalm,
0 as DiscDealTotal,
0 as OtheDealTotal
From HQ_accountingbooks
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;
Error in 11.2.0.4:
ORA-00918: column ambiguously defined
After comparison, all the corresponding table and table structures in the two databases are the same. Why can't I execute normally in 11.2.0.1 but not in 11.2.0.4?
The SQL statement contains the BILLSUBCASE column in two tables:
Select BILLSUBCASE from HQ_accountingbooks;
Select BILLSUBCASE from m_coupon_grant;
Therefore, a. BILLSUBCASE = 5201 is specified to solve the problem.
It seems that 11.2.0.4 has more strict SQL syntax requirements.