Re: locking issue with select for update, sql advice requested

From: laura pena <lizzpenaorclgrp_at_yahoo.com>
Date: Wed, 12 Oct 2005 11:04:34 -0700 (PDT)
Message-ID: <20051012180435.43983.qmail@web35015.mail.mud.yahoo.com>

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: 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_at_iinet.net.au> 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: 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

Received on Wed Oct 12 2005 - 13:06:49 CDT