Re: Can I write this without using Cursors?

>Satar,
>
>There appears to be a potentially serious design problem here.
>
>In cursor C1, the predicate "where rownum < 2" seems to indicate
>that the table PHONEBOOOK is not unique on the column PHONE.
>
>If it is *not* unique, then the update is, shall we say, arbitrary and
>you have no sensible alternative to the way you are doing things now.
>
>If PHONEBOOK.phone *is* unique then the above predicate is unnecessary.
>In this case, the statements given (excluding the "junk" postings) are
>equivalent to the single update statement:
>
> update CALLERS c2
> set (lname, fname, address, city
> ,state, zip, verified, ver_date)
> = (select c1.lastn, c1.firstn, c1.address, c1.city
> c1.state, c1.zip, 'Y', trunc(sysdate)
> from PHONEBOOK c1
> where c1.phone = substr(c2.ani,1,3)||'-'||
> substr(c2.ani,4,3)||'-'||
> substr(c2.ani,7,4) -- This must
 select
>only one row
> )
> where c2.verified is null
> and (c2.lname is null
> or (c2.lname is not null and exists
> (select null from PHONEBOOK where lastn = c2.lname))
> )
>
>Note that, if each value of CALLERS.lname is either null or exists in
>the set of PHONEBOOK.lastn, then the second half of the OR'd predicate
>is unnecessary.
>In this case, the execution consists of a single serial pass through
>CALLERS with a unique indexed access to PHONEBOOK for each qualifying
>row of CALLERS.
>
>HTH
>
>--
>Chrysalis
>
>

Thank You very much for your response, I will sit down with the programmer, and look into this matter.

Satar Naghshineh Received on Tue Jul 29 1997 - 00:00:00 CDT