Listing a person and their nth phone number

Question

I'm using PowerSchool (Oracle DB), and in order to make an export for our emergency robo-calling service, I need to pull a report of students and the top 7 emergency phone numbers associated to them. I have a query that pulls down just the emergency numbers, but am having trouble pulling the nth phone record into the correct context. When I run the current code, I get "ORA-00923: FROM keyword not found where expected".

I can select all the values from the query in the WITH statement and that runs as expected. However, I don't know how to properly grab them in the main query.

WITH emerglist as (
    select emergnumbers.stu_dcid, emergnumbers.contact_priority_order, 1 as phone_order, emergnumbers.contact_phone_1 as phone_number
    from emergnumbers where contact_phone_1 is not null
    UNION
    select emergnumbers.stu_dcid, emergnumbers.contact_priority_order, 2 as phone_order, emergnumbers.contact_phone_2 as phone_number
    from emergnumbers where contact_phone_2 is not null
    UNION
    select emergnumbers.stu_dcid, emergnumbers.contact_priority_order, 3 as phone_order, emergnumbers.contact_phone_3 as phone_number
    from emergnumbers where contact_phone_3 is not null
)
--select * from emerglist;  -- this works

select distinct 
    s.student_number as referencecode,
    s.first_name as firstname,
    s.last_name as lastname,
    --emergency numbers
    (select enum.phone_number from enum where rn=1) as emerg_1
    (select enum.phone_number from enum where rn=2) as emerg_2,
    (select enum.phone_number from enum where rn=3) as emerg_3,
    (select enum.phone_number from enum where rn=4) as emerg_4,
    (select enum.phone_number from enum where rn=5) as emerg_5,
    (select enum.phone_number from enum where rn=6) as emerg_6,
    (select enum.phone_number from enum where rn=7) as emerg_7
from students s
    left outer join (  select emerglist.*, rownum rn from emerglist order by contact_priority_order, phone_order  ) enum on s.dcid = enum.stu_dcid             
where s.enroll_status=0
;

Edit to add: My goal is to have one row per student, with as many columns of emergency numbers filled in as possible, with no gaps (e.g. can't have emerg1 = #, emerg_2 = null, emerg_3 = #). Each student can have 0-10 contacts, each contact can have 0-5 numbers. We are limited to 7 numbers per student. This is a subset of a larger query that also pulls in primary numbers for normal messages like snow days, email addresses, and other demographics. All that works except the emergency numbers. These get used only when there is a true emergency and we want to reach as many numbers as possible.

Sample data from EmergList:

STU_DCID   CONTACT_PRIORITY_ORDER   PHONE_ORDER   PHONE_NUMBER
52  1   1   (222) 222-6476
52  2   1   (222) 555-9649
52  2   2   (222) 888-2212
58  1   1   (222) 222-1734
58  1   2   (222) 555-7222

Desired results:

REFERENCECODE   FIRSTNAME   LASTNAME   EMERG_1   EMERG_2   EMERG_3   EMERG_4   EMERG_5   EMERG_6   EMERG_7
52   John   Smith   (222) 222-6476   (222) 555-9649   (222) 888-2212
58   Mary   Jones   (222) 222-1734   (222) 555-7222

Can you provide sample data and expected output? Think would be a lot better to have a normal join with use of PIVOT instead that many inner selects ... — Veljko89, Nov 04 '19 at 14:49
I've never used Pivot before... I'll research that - thanks! — Melissa, Nov 04 '19 at 15:00
I am still trying to figure out how to embed sample data in this site. — Melissa, Nov 04 '19 at 15:03
You can't embed it, you have to manually write it as text, format it as table and put spaces so site sees it as a code ... take a look at this example https://stackoverflow.com/questions/42955002/sql-server-select-newest-rows-whos-sum-matches-a-value — Veljko89, Nov 04 '19 at 15:07

score 0 · Answer 1 · answered Nov 04 '19 at 15:28

One of the way how you could do this is next ...

select STU_DCID, 
   MAX("1") "1", 
   MAX("2") "2",
   MAX("3") "3",
   MAX("4") "4", 
   MAX("5") "5",
   MAX("6") "6",
   MAX("7") "7", 
   MAX("8") "8",
   MAX("9") "9",
   MAX("10") "10"
from (
   select STU_DCID, 
          CONTACT_PRIORITY_ORDER, 
          PHONE_ORDER, 
          PHONE_NUMBER,
          ROW_NUMBER() OVER (PARTITION BY STU_DCID 
                             ORDER BY CONTACT_PRIORITY_ORDER, PHONE_ORDER) RN
   from EmergList) src
PIVOT (
MAX(PHONE_NUMBER)
FOR RN IN (1 "1", 2 "2", 3 "3",
           4 "4", 5 "5", 6 "6",
           7 "7", 8 "8", 9 "9",
           10 "10")
) GROUP BY STU_DCID;

Then you just change alis of column and add extra join on src in order to get student name ... current result you would get is this

STU_DCID    |       1       |       2        |      3         |4|5|6|7|8|9|10|
    52      |(222) 222-6476 | (222) 555-9649 | (222) 888-2212 | | | | | | | |
    58      |(222) 222-1734 | (222) 555-7222 |                | | | | | | | |

Listing a person and their nth phone number

1 Answers1