-- Create a materialized view used by a number of different views for extracts. 
DECLARE 
    v_count NUMBER; 
BEGIN 
    SELECT Count(*) 
    INTO   v_count 
    FROM   user_objects 
    WHERE  object_type = 'MATERIALIZED VIEW' 
           AND object_name = Upper('PrefsSchoolYearDaily'); 

    IF v_count > 0 THEN 
      EXECUTE IMMEDIATE 'drop materialized view PrefsSchoolYearDaily'; 
    END IF; 
END; 

/ 
CREATE materialized VIEW prefsschoolyeardaily 
build IMMEDIATE 
AS SELECT DISTINCT name, 
                   dbms_lob.Substr(value, 5, 1) AS Value, 
                   schoolid, 
                   yearid 
   FROM   prefs 
   WHERE  Lower(name) = 'att_recordmodedaily' 
          AND value LIKE '1%'; 

CREATE INDEX idx_psyd_schoolid 
  ON prefsschoolyeardaily (schoolid); 

CREATE INDEX idx_psyd_schoolidyearid 
  ON prefsschoolyeardaily (schoolid, yearid); 

CREATE INDEX idx_psyd_yearid 
  ON prefsschoolyeardaily (yearid); 

GRANT SELECT ON prefsschoolyeardaily TO PUBLIC;