Oracle查询错误分析:ORA-01791:不是SELECTed表达式

今天发现了一个特别蹊跷的问题,如下


使用下面语句进行查询

 

 

select distinct iro.opcode
from tblmoroute2op iro
/*left join tblonwip t
on t.mocode = iro.mocode
and t.routecode = iro.routecode
and t.opcode=iro.opcode
and t.orgid = iro.orgid*/
where
iro.MOCODE=(select a.mocode from tblonwip a where a.rcard = 'fb06150004' and rownum=1)
AND iro.routecode=(select a.routecode from tblonwip a where a.rcard = 'fb06150004' and rownum=1)
and iro.orgid = 329
/*and t.actionresult <> 'GOOD'
and iro.iskeyop = 1*/
and exists (select 1
from tblsysparam s
where s.paramgroupcode = 'WASHBOARDTESTOP'
and s.paramcode = iro.opcode
and not exists(select 1 from tblonwip a where a.rcard = 'fb06150004' and a.opcode=iro.opcode and a.actionresult = 'GOOD'))
order by iro.opseq;

报ORA-01791:不是SELECTed表达式

原因是order by后面的opseq字段不在select查询结果字段之中,因为使用了distinct关键字。

 

解决方法
1、将此字段加入到select之后

 

select distinct iro.opcode,iro.opseq
from tblmoroute2op iro
/*left join tblonwip t
on t.mocode = iro.mocode
and t.routecode = iro.routecode
and t.opcode=iro.opcode
and t.orgid = iro.orgid*/
where
iro.MOCODE=(select a.mocode from tblonwip a where a.rcard = 'fb06150004' and rownum=1)
AND iro.routecode=(select a.routecode from tblonwip a where a.rcard = 'fb06150004' and rownum=1)
and iro.orgid = 329
/*and t.actionresult <> 'GOOD'
and iro.iskeyop = 1*/
and exists (select 1
from tblsysparam s
where s.paramgroupcode = 'WASHBOARDTESTOP'
and s.paramcode = iro.opcode
and not exists(select 1 from tblonwip a where a.rcard = 'fb06150004' and a.opcode=iro.opcode and a.actionresult = 'GOOD'))
order by iro.opseq;


2、如果觉得GOODID字段是多余的,实在是不想将它查询出来,可修改为如下:

 

 

select ID,BOOKID,GOODPRICE
from(select distinct ID,BOOKID,GOODPRICE,GOODID from HH_BOOK_GOOD)
order by GOODID;

博文最后更新时间:


评论

  • dobson

    Eca8aT https://www.quora.com/What-the-top-SEO-keywords-for-essay-you-know/answer/Alan-Smith-1772 write my essay

  • johnan

    YH3Ouj http://xnxx.in.net/ xnxx videos

  • dobson

    GKhvFX https://writemyessayforme.web.fc2.com/

  • dobson

    aDJ8HR https://writemyessayforme.web.fc2.com/#writemyessay

  • YrxNH

    Pills prescribing information. What side effects? <a href="https://prednisone4u.top">can you get cheap prednisone without dr prescription</a> in the USA. Everything about medicament. Get information now.

  • sweetpee

    <a href="http://www.mainedj.net/minnesota-vikings-baseball-cap-for-eu-capf">minnesota vikings baseball cap for eu</a> <a href="http://www.masterpharm.net/adidas-pure-boost-gold-and-black-billig-shoesbe">adidas pure boost gold and black billig</a> <a href="http://www.evastat.net/chicago-bears-snapback-hat-billig-hands">chicago bears snapback hat billig</a> <a href="http://www.micksanders.net/nike-air-force-1-flyknit-review-korea-billig-schoenende">nike air force 1 flyknit review korea billig</a> <a href="http://www.markderry.net/matt-ryan-jersey-nflb">matt ryan jersey</a> <a href="http://www.voelzke.net/mercurial-vapor-superfly-8-30-billig-boote">mercurial vapor superfly 8 30 billig</a> [url=http://www.sweetpee.net/]sweetpee[/url]

发表评论

博客统计

访问量:5097478

博文总数:750 评论总数:909180

原创126 翻译20 转载604