ORA-00918:未定义确定列

 

今天在写这个sql的时候报了这个错误,sql如下:

1     select spNumber,userId,glcPassWord,remoteUserId,remotePassWord,serviceNumber,url,mobile,privateLevel,
2     priorityLevel,flowNumber,subSpFlag ,pseudoFlag ,serviceType ,locationStyle,spSinoName ,activeFlag ,
3     delineAtion ,spRange, spRightId ,xyFlag ,xyDisplay, maxNumber ,stateareaCode ,connectflag,locationtype,
4     sagflag,privauthtype,sagid,pushaddr,pushurl,wapurl,bizcustfeetype,bizcustisfree,bizid,createdate,validdate,
5     neednotify from (select a.*,rownum r from
6     (select * from glc_sp_info s,topmcorporation t,glc_service_info se
7      where 1=1 and s.sprightid=t.corporationid and s.spnumber=se.spnumber order by s.spNumber desc) a where rownum <= 12) where r>0

出现该问题的原因:

主要是第七行的(select * 星号 作怪,因为后面关联了3个表,而这三个表中glc_service_info 和glc_sp_info表中都存在spnumber,所以主要原因是出现重复字段。你要把语句改成一下就ok了

select spNumber,userId,glcPassWord,remoteUserId,remotePassWord,serviceNumber,url,mobile,privateLevel,
priorityLevel,flowNumber,subSpFlag ,pseudoFlag ,serviceType ,locationStyle,spSinoName ,activeFlag ,
delineAtion ,spRange, spRightId ,xyFlag ,xyDisplay, maxNumber ,stateareaCode ,connectflag,locationtype,
sagflag,privauthtype,sagid,pushaddr,pushurl,wapurl,bizcustfeetype,bizcustisfree,bizid,createdate,validdate,
neednotify from (select a.*,rownum r from
(select s.spNumber,s.userId,s.glcPassWord,s.remoteUserId,s.remotePassWord,s.serviceNumber,s.url,s.mobile,s.privateLevel,
s.priorityLevel,s.flowNumber,s.subSpFlag ,s.pseudoFlag ,s.serviceType ,s.locationStyle,s.spSinoName ,s.activeFlag ,
s.delineAtion ,s.spRange, s.spRightId ,s.xyFlag ,s.xyDisplay, s.maxNumber ,s.stateareaCode ,s.connectflag,s.locationtype,
s.sagflag,s.privauthtype,s.sagid,s.pushaddr,s.pushurl,s.wapurl,s.bizcustfeetype,s.bizcustisfree,s.bizid,t.createdate,t.validdate,
se.neednotify from glc_sp_info s,topmcorporation t,glc_service_info se
where 1=1 and s.sprightid=t.corporationid and s.spnumber=se.spnumber order by s.spNumber desc) a where rownum <= 12) where r>0

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值