Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Help with SQL outer join; only want latest value
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"
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. Received on Thu Dec 14 2006 - 20:50:31 CST