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.

You don't need an outer join. You don't even need the incomingcalls
table in your from clause, as it is just one field you want to select
from it:

select substr(oc.dialed_number,1,10) "Number",
oc.offhook_time "Time",
oc.duration "Duration",
(select substr(max(iclid_timestamp||iclid_name), 9)
from incomingcalls
where iclid_phone = oc.dialed_number
) "Name"
from outgoingcalls oc
where oc.offhook_time > sysdate - 1

To get the latest iclid_name I concatanate iclid_timestamp with it (say
"20061215The Name"), get the max value and then substring the name from
this expression.

-----------------------------------------------------------------------------------------------
b_addams@yahoo.com schrieb:

> 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.