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
