ORA-00918: column ambiguously defined

最新推荐文章于 2022-04-19 10:36:34 发布

听雨1

最新推荐文章于 2022-04-19 10:36:34 发布

阅读量493

点赞数 1

分类专栏： Oracle 文章标签： ORA-00918 未明确定义列

Oracle 专栏收录该内容

6 篇文章

订阅专栏

本文详细解析了一起在执行SQL查询时遇到的ORA-00918错误，该错误源于列名定义重复。通过深入分析查询语句，发现原因在于查询条件中的列名重复引用，导致在生成临时表时出现列名不明确的问题。文章提供了详细的错误诊断过程和解决方案，帮助开发者理解并解决这类常见数据库错误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天后台在执行SQL查询时报如下错误：
execerror=ORA-00918: column ambiguously defined when generage sheet data:

insert into D_SHEET_DATA (REPORT_ID,SHEET_NO,RATE_DATE,ROW_NO,COLNO1,COLNO2,COLNO3,COLNO4,COLNO5,COLNO6,COLNO7,COLNO8,COLNO9,COLNO10,COLNO11,COLNO12) select 'CTOG_04',1,'201310',rownum,a.* from (with AI as(
select cust_code as cust_code,cust_name as cust_name,sum(account_fee) as account_fee,sum(settle_fee) as settle_fee from
(select b.cust_code as cust_code,b.cust_name as cust_name,sum(account_fee) as account_fee,sum(a.settle_fee) as settle_fee
from D_IIXL_SHARE_RESULT_201310 a,STT_OBJECT b
where a.settle_carrier_id=b.cust_id
group by cust_code,cust_name
)
group by cust_code,cust_name
),

DI as
(select b.cust_code as cust_code,b.cust_name as cust_name,sum(account_fee) as account_fee,sum(a.settle_fee) as settle_fee
from D_IIXL_SHARE_RESULT_201310 a,STT_OBJECT b
where a.settle_carrier_id=7463 and a.settle_carrier_id=b.cust_id
group by cust_code,b.cust_name
)

select AI.cust_code,AI.cust_name,nvl(AI.account_fee,0),nvl(AI.settle_fee,0),
0,0,0,0,nvl(DI.account_fee,0),nvl(DI.settle_fee,0),
nvl(DI.account_fee,0),
nvl(DI.settle_fee,0)
from AI,DI
where AI.CUST_CODE = DI.cust_code(+)
) a

经查：原因是
select AI.cust_code,AI.cust_name,nvl(AI.account_fee,0),nvl(AI.settle_fee,0),
0,0,0,0,nvl(DI.account_fee,0),nvl(DI.settle_fee,0),
nvl(DI.account_fee,0),
nvl(DI.settle_fee,0)
from AI,DI
where AI.CUST_CODE = DI.cust_code(+)

在查询的时候列明重复了，导致在查询还有重复列明的临时表时会报“ORA-00918: column ambiguously defined 未明确定义列”的错误信息