-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Bob was promoted to Director of the Computer Center
-- 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.11 in Oracle 7.3
REM

INSERT INTO INCUMBENTS
  (SSN , PCN , START_DATE , END_DATE)
SELECT '111223333' , '908739' , SYSDATE , END_DATE
FROM   INCUMBENTS
WHERE  SSN = '111223333' 
  AND  START_DATE <= SYSDATE
  AND  END_DATE > SYSDATE;

UPDATE INCUMBENTS
SET    END_DATE = SYSDATE
WHERE  SSN = '111223333'
   AND PCN != '908739'
   AND START_DATE < SYSDATE
   AND END_DATE > SYSDATE;

UPDATE INCUMBENTS
SET    PCN = '908739'
WHERE  SSN = '111223333'
   AND START_DATE >= SYSDATE;
