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