Re: Help with SQL outer join; only want latest value

From: Thorsten Kettner <thorsten.kettner_at_web.de>
Date: 15 Dec 2006 00:12:06 -0800
Message-ID: <1166170326.824144.303730@73g2000cwn.googlegroups.com>

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_at_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.
Received on Fri Dec 15 2006 - 02:12:06 CST