I was thinking ... Did this guys not read my response what is he thinking giving me a complex query ....
I am SOO sorry for thinking that .. this actually seems to be working :) I guess it is not considered any of the following listed in the oracle error message. >ORA-02014 (See ORA-02014.ora-code.com): cannot select FOR UPDATE from view with > DISTINCT, GROUP BY, etc.
I'm putting it in and unit testing the following now. SQL> select callid 2 , to_char(calldate,'MMDDYYYY HH24:MM:SS') 3 from calldetail_tmp 4 where callid in ( 5 select callid 6 from ( 7 select callid 8 from calldetail_tmp 9 where calldate between sysdate - 110 and sysdate 10 and audiostate = 10 11 and callflowtypeid in (8,13,17,28) 12 order by calldate 13 ) 14 where rownum <= 4 15 ) for update;
You rock if this works.
Many thanks, -Lizz
--- Anthony Wilson <amwilson@(protected)> wrote:
> Aha... that wasn't clear in the original. > > How about something like: > > select callid > , to_char(calldate,'MMDDYYYY HH24:MM:SS') > from calldetail_tmp > where callid in ( > select callid > from ( > select callid > from calldetail_tmp > where calldate between sysdate - 110 and > sysdate > and audiostate = 10 > and callflowtypeid in (8,13,17,28) > order by calldate > ) > where rownum <= 4 > ) > / > > On Wed, Oct 12, 2005 at 09:25:07AM, laura pena > 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 (See ORA-02014.ora-code.com): 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 >
__ ____ ____ ____ ____ ____ ______ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- http://www.freelists.org/webpage/oracle-l
