-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Bob was promoted to Director of the Computer
-- Center for 1997.
-- INCUMBENTS is temporal
-- By   : Bruce C. Huang
-- Date : Dec. 12, 1997
--
-- Oracle 7.3 cannot handle INSERT..VALUES..WHERE,
-- so the SQL92 format was modified to INSERT..
-- SELECT..WHERE
-- -----------------------------------------------
REM
REM Code Fragment 7.17 in Oracle 7.3
REM

INSERT INTO INCUMBENTS
  (SSN , PCN , START_DATE , END_DATE)
SELECT '111223333' , PCN , START_DATE, TO_DATE('1997-01-01','YYYY-MM-DD')
FROM   INCUMBENTS
WHERE  SSN = '111223333' 
  AND  START_DATE < TO_DATE('1997-01-01','YYYY-MM-DD')
  AND  END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD');

INSERT INTO INCUMBENTS
  (SSN , PCN , START_DATE , END_DATE)
SELECT '111223333' , PCN , TO_DATE('1998-01-01','YYYY-MM-DD') , END_DATE
FROM   INCUMBENTS
WHERE  SSN = '111223333' 
  AND  START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
  AND  END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET    PCN = '908654'
WHERE  SSN = '111223333'
   AND START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET    START_DATE = TO_DATE('1997-01-01','YYYY-MM-DD')
WHERE  SSN = '111223333'
   AND START_DATE < TO_DATE('1997-01-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET    END_DATE = TO_DATE('1998-01-01','YYYY-MM-DD')
WHERE  SSN = '111223333'
   AND START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
   AND END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD');

