What db version?? I probably missed that in op. Raj On 10/12/05, laura pena <lizzpenaorclgrp@xxxxxxxxx> wrote: > > > 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> / > > CALLID TO_CHAR(CALLDATE, > ---------------- ----------------- > 5020050630008657 06302005 09:06:24 > 5020050630008658 06302005 09:06:30 > 5020050630008659 06302005 10:06:13 > 5020050630008660 06302005 10:06:58 > > okay so lets add the for udpate now .... > > > 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 > > > ERROR at line 2: > ORA-02014: cannot select FOR UPDATE from view with > DISTINCT, GROUP BY, etc. > > > 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@xxxxxxxxxxxx> 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 > -- > //www.freelists.org/webpage/oracle-l > -- ------------------------------ select standard_disclaimer from company_requirements where category = 'MANDATORY';