Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Help with SQL outer join; only want latest value

Help with SQL outer join; only want latest value

From: <b_addams_at_yahoo.com>
Date: 14 Dec 2006 18:50:31 -0800
Message-ID: <1166151031.777463.31450@80g2000cwy.googlegroups.com>


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. Received on Thu Dec 14 2006 - 20:50:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US