Hello all. I have an application that displays phone call information.
The app has been working for a while but today I noticed a glitch.
There are two tables in the select statement: incomingcalls and
outgoingcalls. The incomingcalls table also includes name information,
where the outgoingcalls table only has the number and call duration.
The query displays the number, time and duration from the outgoingcalls
table and then looks up the name information from the incomingcalls
table. Of course, there is not always a record in the incomingcalls
table so I used an outer join. The problem I noticed today is that
there was one too many calls. When I looked in detail I found that one
call was listed twice. Apparently, this phone number had more than one
"name" value in the incomingcalls table, so my "select distinct"
displayed both. What I woudl prefer is to have the lookup into the
incomingcalls table just grab the latest value.

Here is the table information:

SQL> desc incomingcalls
Name Null? Type
----------------------------------------- --------
----------------------------
ICLID_PHONE NOT NULL VARCHAR2(15)
ICLID_NAME VARCHAR2(15)
ICLID_TIMESTAMP NOT NULL VARCHAR2(8)
SYSTEM_TIMESTAMP NOT NULL DATE
LINE_NUMBER VARCHAR2(2)
ICLID_RAW VARCHAR2(120)
PHONE_ID NUMBER
DURATION NUMBER(38)
OFFHOOK_TIME DATE
ONHOOK_TIME DATE

SQL> desc outgoingcalls
Name Null? Type
----------------------------------------- --------
----------------------------
OFFHOOK_TIME NOT NULL DATE
ONHOOK_TIME DATE
DIALED_NUMBER VARCHAR2(128)
LINE_NUMBER VARCHAR2(1)
DURATION NUMBER(38)


Here is the SQL:

select substr(oc.dialed_number,1,10) "Number",
oc.offhook_time "Time",
oc.duration "Duration",
ic.iclid_name "Name"
from outgoingcalls oc,
(select distinct iclid_phone, iclid_name from incomingcalls ) ic
where oc.offhook_time > sysdate - 1
and
oc.dialed_number = ic.iclid_phone (+)


I tried several things to fix this. One wsa to get rid of the
"distinct" keyword and try to use "rownum <= 1" in the incomingcalls
portion. While this produced the correct number of rows, the name field
was blank.

Any thoughts?

Thanks.