Home » SQL & PL/SQL » SQL & PL/SQL » retrieve one value for phone number (10.5.2)
retrieve one value for phone number [message #444933] Wed, 24 February 2010 21:24 Go to next message
sriram.aus
Messages: 3
Registered: February 2010
Junior Member
Hi

I need to retrieve only one telephone number against the parent id. The preference is to get mobile if available if not home number else work phone. How can I achieve this

I have given the sql code and sample output below
Thanks in advance

Regards
Sriram

select
pp.phone_id
,pp.parent_id
,pp.phone_type
,pp.phone_number

from per_phones pp
,per_all_people_f papf
,per_contact_relationships pcr

where papf.person_id = pp.parent_id
and pcr.person_id = papf.person_id
and trunc(sysdate) between trunc(nvl(pcr.date_start, sysdate-1))
and trunc(nvl(pcr.date_end, sysdate+1))

and pcr.primary_contact_flag = 'Y'
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between nvl(pp.date_from, sysdate-1) and nvl(pp.date_to, sysdate+1)
and papf.person_id in (146564, 2500);

Output
PHONE_ID PARENT_ID PHONE_TYPE PHONE_NUMBER
12960 2500 H1 9385789
141997 146564 H1 2145789
311028 146564 H2 124589
311029 146564 W1 01738975
311108 146564 M 04879652

Actual results should be
PHONE_ID PARENT_ID PHONE_TYPE PHONE_NUMBER
12960 2500 H1 9385789
311108 146564 M 04879652
Re: retrieve one value for phone number [message #444944 is a reply to message #444933] Wed, 24 February 2010 22:11 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ give the compete test case
2/ format your code.


google for case or decode function in oracle.


select ROWNUM , CASE ROWNUM 
             WHEN 1 THEN 'ONE' 
             WHEN 2 THEN 'TWO' 
             ELSE 'INVALID' 
             END FLD 
FROM DUAL CONNECT BY LEVEL < 6
/

    ROWNUM FLD
---------- -------
         1 ONE
         2 TWO
         3 INVALID
         4 INVALID
         5 INVALID
Re: retrieve one value for phone number [message #444945 is a reply to message #444933] Wed, 24 February 2010 22:14 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Will you please show us, what you have tried?
What output you got?
Where have you stucked up?
Can you please format your code using [CODE] tags?

regards,
Delna
Re: retrieve one value for phone number [message #444947 is a reply to message #444933] Wed, 24 February 2010 22:29 Go to previous messageGo to next message
sriram.aus
Messages: 3
Registered: February 2010
Junior Member
Hi

Thanks for your reply

Data contains
Person X - 2 telephone numbers one mobile and one home
Person Y - home number and work phone

I have to retrieve mobile if available else home number if both not available then I have to retrieve work number

I am unable to upload formatted output as such I have given my code below for your review

SELECT pp.phone_id, 
       pp.parent_id, 
       pp.phone_type, 
       pp.phone_number 
FROM   per_phones pp, 
       per_all_people_f papf, 
       per_contact_relationships pcr 
WHERE  papf.person_id = pp.parent_id 
       AND pcr.person_id = papf.person_id 
       AND Trunc(sysdate) BETWEEN Trunc(Nvl(pcr.date_start,sysdate - 1)) AND Trunc(Nvl(pcr.date_end,sysdate + 1)) 
       AND pcr.primary_contact_flag = 'Y' 
       AND Trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date 
       AND Trunc(sysdate) BETWEEN Nvl(pp.date_from,sysdate - 1) AND Nvl(pp.date_to,sysdate + 1) 
       AND papf.person_id IN (146564,2500); 


Re: retrieve one value for phone number [message #444948 is a reply to message #444947] Wed, 24 February 2010 22:35 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
FROM   per_phones pp, 
       per_all_people_f papf, 
       per_contact_relationships pcr


both PER_ALL_PEOPLE_F & PER_CONTACT_RELATIONSHIPS can be eliminated out of the FROM clause since they contribute no data to the SELECT clause.

Both tables can be subordinated into the WHERE clause
Re: retrieve one value for phone number [message #444949 is a reply to message #444933] Wed, 24 February 2010 22:43 Go to previous messageGo to next message
sriram.aus
Messages: 3
Registered: February 2010
Junior Member
Hi

In order to shorten the query I have removed the other columns from those underlying 2 tables

Regards
sriram

Re: retrieve one value for phone number [message #445010 is a reply to message #444949] Thu, 25 February 2010 03:52 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
As I am not having access to SQL *Plus right now, I am working with Oracle ApEx utility.

So that I can not copy paste my session.

But with dummy data as
parent(id, name)
phone(id, typ, phone)

following is the query which gives you required output.
select id, 
       name, 
       case when M != '-' then 'Mobile' when O != '-' then 'Office' when H != '-' then 'Home' end Typ,
       case when M != '-' then M when O != '-' then O when H != '-' then H end Nmbr
from (
select * from 
 (select pr.id, pr.name, ph.typ, ph.phone
 from parent pr
 inner join phone ph on pr.id=ph.id)
pivot (max(phone) for typ in ('M' as M,'O' as O,'H' as H)))


where typ values are 'M','O','H' for Mobile, Office, Home respectively.

Revert back, is any issue.

regards,
Delna
Previous Topic: group by problem
Next Topic: Monitoring the status of a pl/sql procedure (merged 7)
Goto Forum:
  


Current Time: Mon Dec 05 08:47:18 CST 2016

Total time taken to generate the page: 0.10082 seconds