You can not do a select for update on a complex ora aggregate query.
Should mention the java application is multi-threaded this is why we want to keep the lock as one query.
Many Thanks, -Lizz
--- Paul Drake <bdbafh@(protected)> wrote:
> what version of the Oracle Database? > most likely you want to use that for your inner > query (without the for > update) and select against it with the rownum > filter. > > On 10/12/05, laura pena <lizzpenaorclgrp@(protected)> > wrote: > > > > I would like to create a lock in my Java > application > > using select for update. > > > > Here is the sql: > > > > select callid, TO_CHAR(calldate,'MMDDYYYY > HH24:MM:SS') > > from calldetail_tmp > > where calldate between sysdate -110 and sysdate > > and (audiostate = 10 AND callflowtypeid IN > > (8,13,17,28)) > > and rownum <= 4 > > order by calldate for update; > > > > > > Issue hear rownum does not guarantee results will > be > > in order specified by the order by clause. Rownum > is > > set before sort is done by order clause. > > > > Most of the time calls are returned in sorted > order ( > > order of insertion is what rownum is being > returned > > as). It is in a backlog condition that rows are > not > > returned properly. > > > > > > I have looked at locking via a view but can not > lock > > on a complex view. Am looking at lock table in > share > > mode now. Any suggestions would be greatly > > appreciated. > > > > > > Many Thanks, > > -Lizz > > > > > > > > > > __ ____ ____ ____ ____ ____ ______ > > Yahoo! Mail - PC Magazine Editors' Choice 2005 > > http://mail.yahoo.com > > -- > > http://www.freelists.org/webpage/oracle-l > > > > > > -- > #/etc/init.d/init.cssd stop > # f=ma, divide by 1, convert to moles. >
__ ____ ____ ____ ____ ____ ______ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ -- http://www.freelists.org/webpage/oracle-l
