-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Sequenced Primary Key for INCUMBENTS
-- 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.3 in Oracle 7.3
REM
INSERT INTO INCUMBENTS
   ( SSN , PCN , START_DATE , END_DATE )
SELECT '111223333' SSN , '341288' PCN , SYSDATE , 
       TO_DATE('3000-01-01','YYYY-MM-DD')
FROM   DUAL
WHERE  NOT EXISTS( SELECT *
                   FROM   INCUMBENTS I2
                   WHERE  I2.SSN = SSN
                      AND I2.END_DATE = TO_DATE('3000-01-01','YYYY-MM-DD'))
       AND EXISTS( SELECT *
                   FROM  POSITIONS
                   WHERE POSITIONS.PCN = PCN
                     AND POSITIONS.END_DATE = TO_DATE('3000-01-01','YYYY-MM-DD'));
