Here is the issue with this sql: 1 select * 2 from ( 3 select callid 4 , to_char(calldate,'MMDDYYYY HH24:MM:SS') 5 from calldetail_tmp 6 where calldate between sysdate - 110 and sysdate 7 and audiostate = 10 8 and callflowtypeid in (8,13,17,28) 9 order by calldate 10 ) 11* where rownum <= 4 SQL> /
Add the for update: 1 select * 2 from ( 3 select callid 4 , to_char(calldate,'MMDDYYYY HH24:MM:SS') 5 from calldetail_tmp 6 where calldate between sysdate - 110 and sysdate 7 and audiostate = 10 8 and callflowtypeid in (8,13,17,28) 9 order by calldate 10 ) 11 where rownum <= 4 12* for update
Issue with locking is the application is multi-threaded , if I break up this up into 2 sql statements two threads could update the same row. I don't want this to happen.
Many Thanks, -Lizz
--- Anthony Wilson <amwilson@(protected)> wrote:
> For the ordering issue, just move the rownum > predicate out of the query: > > select * > from ( > select callid > , to_char(calldate,'MMDDYYYY HH24:MM:SS') > from calldetail_tmp > where calldate between sysdate - 110 and sysdate > and audiostate = 10 > and callflowtypeid (8,13,17,28) > order by calldate > ) > where rownum <= 4 > / > > As for the locking issue, I'm not sure I understood > the question...?? > > cheers, > Anthony > > On Wed, Oct 12, 2005 at 08:34:23AM, laura pena > 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 > > > -- > Anthony Wilson >
__ ____ ____ ____ ____ ____ ______ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- http://www.freelists.org/webpage/oracle-l
