-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Bob was assigned the position of Associate Director
-- of the Computer Center for 1997, also ensuring
-- referential integrity.
-- 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.14 in Oracle 7.3
REM
INSERT INTO INCUMBENTS
   ( SSN , PCN , START_DATE , END_DATE )
SELECT '111223333' SSN , '341288' PCN , TO_DATE('1997-01-01','YYYY-MM-DD') , 
       TO_DATE('1998-01-01','YYYY-MM-DD')
FROM   DUAL
WHERE  NOT EXISTS( SELECT *
                   FROM   INCUMBENTS I2
                   WHERE  I2.SSN = SSN
                      AND I2.START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
                      AND I2.END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD'))
   AND EXISTS( SELECT *
               FROM   POSITIONS P
               WHERE  P.PCN = PCN
                  AND P.START_DATE <= TO_DATE('1997-01-01','YYYY-MM-DD')
                  AND P.END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD'))
   AND EXISTS( SELECT *
               FROM  POSITIONS P
               WHERE  P.PCN = PCN
                  AND P.START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
                  AND P.END_DATE >= TO_DATE('1998-01-01','YYYY-MM-DD'))
   AND NOT EXISTS( SELECT *
                   FROM  POSITIONS P
                   WHERE P.PCN = PCN
                     AND P.END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD')
                     AND P.END_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
                     AND NOT EXISTS( SELECT *
                                     FROM  POSITIONS P2
                                     WHERE P2.PCN = PCN
                                       AND P2.START_DATE <= P.END_DATE
                                       AND P.END_DATE < P2.END_DATE ));

