But what if they don’t have a telephone number?

Recently I’ve been tasked to run some Oracle reports on students that don’t have Comprehensive Education Plan. We have a Banner (Ellucian Product) Oracle database (data warehouse) that has millions of records, and there can be a large number of data to filter.

For some of the students, they may not have a telephone number stored in Banner, so I needed to add a way to print out some default value (of zeros) to show that in the report. I had to use PL/SQL to run my reports, I found using standard queries, it was almost impossible to create the reports I need, so I had to use PL/SQL. This article describes how I used PL/SQL to achieve what I needed.

DECLARE
   phonenum integer := 1;
   TEL SPRTELE%rowtype;
   ...
BEGIN
   ...
   -- Telephone.
   SELECT count(*) into phonenum
   FROM SPRTELE
   WHERE SPRTELE_PIDM = stu_pidm.SPRIDEN_PIDM;
   IF phonenum < 1 THEN
      TEL.SPRTELE_PHONE_AREA := '000';
      TEL.SPRTELE_PHONE_NUMBER := '0000000';
   ELSE
      SELECT * INTO TEL
      FROM SPRTELE
      WHERE rownum = 1
      AND SPRTELE_SEQNO = (
         SELECT MAX(T2.SPRTELE_SEQNO)
         FROM SPRTELE T2
         WHERE T2.SPRTELE_PIDM = stu_pidm.SPRIDEN_PIDM
      )
      AND SPRTELE_PIDM = stu_pidm.SPRIDEN_PIDM;
   END IF;
   ...

The variable phonenum stores the number of rows queried, if it’s zero, there’s no telephone. Then set the phone to zeros (default). Otherwise get the largest sequence and the first row of that as the telephone number.

Hopefully that all makes sense…

Advertisement