1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

retrieve one telephone number

Discussion in 'SQL PL/SQL' started by sriram.aus, Feb 25, 2010.

  1. sriram.aus

    sriram.aus Guest

    Hi

    There is a requirement for me to retrieve only one phone number the preference is if mobile available M. if not Home number - H1 else Work phone W1.

    I have given the code and output below

    Regards
    Sriram

    Code (SQL):
    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);
     
    PHONE_ID PARENT_ID PHONE_TYPE PHONE_NUMBER
    12960 2500 H1 3567898
    141997 146564 H1 5478970
    311028 146564 H2 125789
    311029 146564 W1 1234893
    311108 146564 M 04578950

    Output should look like
    PHONE_ID PARENT_ID PHONE_TYPE PHONE_NUMBER
    12960 2500 H1 3567898
    311108 146564 M 04578950
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    One methode would be to make a virtual coulum for preference and to pick the highest preference record (least preference value)

    Code (SQL):

    SELECT
    pp.phone_id
    ,pp.parent_id
    ,pp.phone_type
    ,pp.phone_number
    , DECODE(pp.phone_type,'M',1,'H1',2,'W1',3,'H2',4,5) preference

    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);