-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Bob was promoted to Director of the Computer
-- Center (current version).
-- INCUMBENTS and POSITIONS are 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.21 in Oracle 7.3
REM
INSERT INTO INCUMBENTS
   ( SSN , PCN , START_DATE , END_DATE )
SELECT '111223333', POSITIONS.PCN, SYSDATE, POSITIONS.END_DATE
FROM   INCUMBENTS, POSITIONS, JOB_TITLES
WHERE  SSN = '111223333'
   AND INCUMBENTS.START_DATE <= SYSDATE
   AND INCUMBENTS.END_DATE > SYSDATE
   AND POSITIONS.JOB_TITLE_CODE1 = JOB_TITLE_CODE
   AND JOB_TITLE = 'Director, Computer Center'
   AND POSITIONS.START_DATE <= SYSDATE
   AND SYSDATE < POSITIONS.END_DATE;

UPDATE INCUMBENTS
SET   END_DATE = SYSDATE
WHERE SSN = '111223333'
  AND PCN != ( SELECT PCN
               FROM   POSITIONS, JOB_TITLES
               WHERE  POSITIONS.JOB_TITLE_CODE1 = JOB_TITLE_CODE
                  AND JOB_TITLE = 'Director, Computer Center'
                  AND POSITIONS.START_DATE <= SYSDATE
                  AND SYSDATE < POSITIONS.END_DATE)
  AND INCUMBENTS.START_DATE < SYSDATE
  AND INCUMBENTS.END_DATE > SYSDATE;

UPDATE INCUMBENTS
SET    PCN = ( SELECT PCN
               FROM   POSITIONS, JOB_TITLES
               WHERE  POSITIONS.JOB_TITLE_CODE1 = JOB_TITLE_CODE
                  AND JOB_TITLE = 'Director, Computer Center'
                  AND POSITIONS.START_DATE <= SYSDATE
                  AND SYSDATE < POSITIONS.END_DATE)
WHERE  SSN = '111223333'
   AND START_DATE >= SYSDATE;

