如何处理错误"Ambiguous Columns Defined"？

Question

这是我的sql命令：

select INCOME_TYPE_ID,
       REGION_CODE,
       FIN_YEAR_CODE,
       PORTION_AMOUNT
  from INCOME.INCOME_TYPE, 
       COMMON.REGION, 
       INCOME.RECEIVE_DOC_PORTION, 
       INCOME.ASSESS_ORDER_ITEM, 
       ACCOUNTING.VOUCHER_ITEM_RECEIVE_DOC, 
       ACCOUNTING.VOUCHER_ITEM, 
       ACCOUNTING.VOUCHER, 
       ACCOUNTING.FIN_YEAR
 where INCOME.RECEIVE_DOC_PORTION.ASSESS_ORDER_ITEM_ID = INCOME.ASSESS_ORDER_ITEM.ASSESS_ORDER_ITEM_ID
   and INCOME.ASSESS_ORDER_ITEM.INCOME_TYPE_ID=INCOME.INCOME_TYPE.INCOME_TYPE_ID
   and INCOME.RECEIVE_DOC_PORTION.RECEIVE_DOC_PORTION_ID = ACCOUNTING.VOUCHER_ITEM_RECEIVE_DOC.RECEIVE_DOC_PORTION_ID
   and ACCOUNTING.VOUCHER_ITEM_RECEIVE_DOC.VOUCHER_ITEM_ID = ACCOUNTING.VOUCHER_ITEM.VOUCHER_ITEM_ID
   and ACCOUNTING.VOUCHER_ITEM.VOUCHER_ID = ACCOUNTING.VOUCHER.VOUCHER_ID
   and ACCOUNTING.VOUCHER.REGION_CODE = COMMON.REGION.REGION_CODE
   and ACCOUNTING.VOUCHER.FIN_YEAR_CODE = ACCOUNTING.FIN_YEAR.FIN_YEAR_CODE

我得到了这个错误：

定义的列不明确

我正在使用SQL Developer作为Oracle客户端。

Answer 1

显然，选择列表中的一个（或多个）列名存在于FROM列表的多个表中。

您需要在SELECT列表中的每一列前面加上它来自的表（始终这样做也是一种很好的做法，不管它们是否是模糊的）

Answer 2

在选择查询的每列之前提到表的名称。列不明确意味着在某个SELECT语句中有多个具有相同名称的列。

Answer 3

试试这个方法，用它们的完全限定名称预给定所有选定列（就像您在SELECT中的其他地方所做的那样）：

select INCOME.INCOME_TYPE.INCOME_TYPE_ID,
       COMMON.REGION.REGION_CODE,
       ACCOUNTING.FIN_YEAR.FIN_YEAR_CODE,
       ACCOUNTING.VOUCHER_ITEM_RECEIVE_DOC.PORTION_AMOUNT
  from INCOME.INCOME_TYPE, 
       COMMON.REGION, 
       INCOME.RECEIVE_DOC_PORTION, 
       INCOME.ASSESS_ORDER_ITEM, 
       ACCOUNTING.VOUCHER_ITEM_RECEIVE_DOC, 
       ACCOUNTING.VOUCHER_ITEM, 
       ACCOUNTING.VOUCHER, 
       ACCOUNTING.FIN_YEAR
 where INCOME.RECEIVE_DOC_PORTION.ASSESS_ORDER_ITEM_ID = INCOME.ASSESS_ORDER_ITEM.ASSESS_ORDER_ITEM_ID
   and INCOME.ASSESS_ORDER_ITEM.INCOME_TYPE_ID = INCOME.INCOME_TYPE.INCOME_TYPE_ID
   and INCOME.RECEIVE_DOC_PORTION.RECEIVE_DOC_PORTION_ID = ACCOUNTING.VOUCHER_ITEM_RECEIVE_DOC.RECEIVE_DOC_PORTION_ID
   and ACCOUNTING.VOUCHER_ITEM_RECEIVE_DOC.VOUCHER_ITEM_ID = ACCOUNTING.VOUCHER_ITEM.VOUCHER_ITEM_ID
   and ACCOUNTING.VOUCHER_ITEM.VOUCHER_ID = ACCOUNTING.VOUCHER.VOUCHER_ID
   and ACCOUNTING.VOUCHER.REGION_CODE = COMMON.REGION.REGION_CODE
   and ACCOUNTING.VOUCHER.FIN_YEAR_CODE = ACCOUNTING.FIN_YEAR.FIN_YEAR_CODE

我不得不猜测这匹雌马符合的名字

ACCOUNTING.VOUCHER_ITEM_RECEIVE_DOC.PORTION_AMOUNT

可能是

INCOME.RECEIVE_DOC_PORTION.PORTION_AMOUNT

但你应该能够很容易地解决这个问题。

希望它能帮助。。。