Oracle 查询SQL语句报 ORA-00918: column ambiguously defined(Navicat中)

原有的SQL语句是:

SELECT
	* 
FROM
	(
	SELECT
		i.ID,
		i.NAME,
		i.code,
		i.num,
		i.state,
		i.unit,
		i.TYPE,
		i.addtime,
		i.TIME,
		i.userid,
		i.lastmodified,
		i.modifiedby,
		i.isdel,
		i.deptcode,
		i.manufacturer,
		i.productiondate,
		i.stationid,
		i.OFFICEID,
		s.NAME storeid,
		H.NAME houseid,
		A.NAME areaid 
	FROM
		AE_INVOICING_INVENTORY i,
		AD_YJZY_STOREHOUSE s,
		AD_YJZY_HOUSE H,
		AD_YJZY_AREA A 
	WHERE
		i.storeid = s.ID ( + ) 
		AND i.houseid = H.ID ( + ) 
		AND i.areaid = A.ID ( + ) 
	) T 
WHERE
	1 = 1 
	AND T.isdel = 0 
	AND deptcode IN (
	SELECT DISTINCT
		( sd.RANGE ) AS depcode 
	FROM
		sys_user su,
		sys_depart_user sdu,
		sys_depart sd 
	WHERE
		su.ID = sdu.userid 
		AND sd.ID = sdu.departid 
		AND su.ID = '022bc57b-5b83-43f4-9baf-b37d7782ca03' UNION
	SELECT DISTINCT
		( ydr.dataid ) AS depcode 
	FROM
		sys_user su,
		sys_role sr,
		sys_user_role sur,
		yw_data_role ydr 
	WHERE
		su.ID = sur.userid 
		AND sr.ID = sur.roleid 
		AND ydr.roleid = sr.ID 
		AND su.ID = '022bc57b-5b83-43f4-9baf-b37d7782ca03' 
	) 
ORDER BY
	T.addtime DESC

后来因为需求的原因得改变查询 i 表中所有的字段,变成i.*,因为使用Oracle经验少,导致错误,大家一起看一下!

SELECT
	* 
FROM
	(
	SELECT
		i.*,
		s.NAME storeid,
		H.NAME houseid,
		A.NAME areaid 
	FROM
		AE_INVOICING_INVENTORY i,
		AD_YJZY_STOREHOUSE s,
		AD_YJZY_HOUSE H,
		AD_YJZY_AREA A 
	WHERE
		i.storeid = s.ID ( + ) 
		AND i.houseid = H.ID ( + ) 
		AND i.areaid = A.ID ( + ) 
	) T 
WHERE
	1 = 1 
	AND T.isdel = 0 
	AND deptcode IN (
	SELECT DISTINCT
		( sd.RANGE ) AS depcode 
	FROM
		sys_user su,
		sys_depart_user sdu,
		sys_depart sd 
	WHERE
		su.ID = sdu.userid 
		AND sd.ID = sdu.departid 
		AND su.ID = '022bc57b-5b83-43f4-9baf-b37d7782ca03' UNION
	SELECT DISTINCT
		( ydr.dataid ) AS depcode 
	FROM
		sys_user su,
		sys_role sr,
		sys_user_role sur,
		yw_data_role ydr 
	WHERE
		su.ID = sur.userid 
		AND sr.ID = sur.roleid 
		AND ydr.roleid = sr.ID 
		AND su.ID = '022bc57b-5b83-43f4-9baf-b37d7782ca03' 
	) 
ORDER BY
	T.addtime DESC

 这个在Navicat中运行就出错> ORA-00918: column ambiguously defined,我弄了好长时间,我也在网上查过这个错误原因说是字段某列定义模糊,后来因为我华伟哥一句话把我点醒了,于是我就把别名修改了一下,后来一下成功了,原因就是别名和i 表中的字段冲突了,接下来看一下正确SQL语句:

SELECT
	* 
FROM
	(
	SELECT
		i.*,
		s.NAME AS storeid11,
		H.NAME houseid11,
		A.NAME areaid11 
	FROM
		AE_INVOICING_INVENTORY i,
		AD_YJZY_STOREHOUSE s,
		AD_YJZY_HOUSE H,
		AD_YJZY_AREA A 
	WHERE
		i.storeid = s.ID ( + ) 
		AND i.houseid = H.ID ( + ) 
		AND i.areaid = A.ID ( + ) 
	) T 
WHERE
	1 = 1 
	AND T.isdel = 0 
	AND deptcode IN (
	SELECT DISTINCT
		( sd.RANGE ) AS depcode 
	FROM
		sys_user su,
		sys_depart_user sdu,
		sys_depart sd 
	WHERE
		su.ID = sdu.userid 
		AND sd.ID = sdu.departid 
		AND su.ID = '022bc57b-5b83-43f4-9baf-b37d7782ca03' UNION
	SELECT DISTINCT
		( ydr.dataid ) AS depcode 
	FROM
		sys_user su,
		sys_role sr,
		sys_user_role sur,
		yw_data_role ydr 
	WHERE
		su.ID = sur.userid 
		AND sr.ID = sur.roleid 
		AND ydr.roleid = sr.ID 
		AND su.ID = '022bc57b-5b83-43f4-9baf-b37d7782ca03' 
	) 
ORDER BY
	T.addtime DESC

整个过程的变化过程,短短的变化过程,花费了很长时间,还是经验太少啊!

过程图:

大神勿喷,欢迎留言! 

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值