SQL & PL/SQL

Hi

In the following sample query I need to retrieve only one phone number. The preference is if Mobile available - mobile number if not Home number (Type - H1), else Work number (W1).

I have included the script and sample output what I am getting from the SQL

Thanks in advance

Regards
Sriram

select pp.ph_id
,papf.person_id
,pp.ph_type
,pp.ph_no
,pp.date_from
,pp.date_to
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 papf.person_id in (146564, 514)
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);

output

PH_ID	PERSON_ID	PH_TYPE	PH_NO	DATE_FROM	DATE_TO
3261	514		H1		987524879	13-Jun-04	
117287	514		M		0403672797	11-May-05	
141997	146564		H1		54789620	15-Feb-06	05-Mar-10
311028	146564		H2		235469		5-Dec-09	
311029	146564		W1		8300054		15-Dec-09	
311108	146564		M		04126872	12-Feb-10