+ echo Cleanup Cleanup + db2 DROP TABLE LOT_LOC DB20000I The SQL command completed successfully. + db2 CREATE TABLE LOT_LOC(FDYD_ID INT, LOT_ID_NUM INT, PEN_ID INT, HD_CNT INT, FROM_DATE DATE, TO_DATE DATE) DB20000I The SQL command completed successfully. + db2 INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE) VALUES (1, 137, 1, 17, DATE('1998-02-07'), DATE ('1998-02-18')) DB20000I The SQL command completed successfully. + db2 INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE) VALUES (1, 219, 1, 43, DATE('1998-02-25'), DATE('1998-03-01')) DB20000I The SQL command completed successfully. + db2 INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE) VALUES (1, 219, 1, 20, DATE('1998-03-01'), DATE('1998-03-14')) DB20000I The SQL command completed successfully. + db2 INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE) VALUES (1, 219, 2, 23, DATE('1998-03-01'), DATE('1998-03-14')) DB20000I The SQL command completed successfully. + db2 INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE) VALUES (1, 219, 2, 43, DATE('1998-03-14'), DATE('9999-12-31')) DB20000I The SQL command completed successfully. + db2 INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE) VALUES (1, 374, 1, 14, DATE('1998-02-20'), DATE('9999-12-31')) DB20000I The SQL command completed successfully. + db2 SELECT PEN_ID, HD_CNT FROM LOT_LOC WHERE FDYD_ID = 1 AND LOT_ID_NUM = 219 AND TO_DATE = DATE('9999-12-31') PEN_ID HD_CNT ----------- ----------- 2 43 1 record(s) selected. + db2 SELECT PEN_ID, HD_CNT, FROM_DATE, TO_DATE FROM LOT_LOC WHERE FDYD_ID = 1 AND LOT_ID_NUM = 219 PEN_ID HD_CNT FROM_DATE TO_DATE ----------- ----------- ---------- ---------- 1 43 02/25/1998 03/01/1998 1 20 03/01/1998 03/14/1998 2 23 03/01/1998 03/14/1998 2 43 03/14/1998 12/31/9999 4 record(s) selected. + db2 SELECT PEN_ID, HD_CNT FROM LOT_LOC WHERE FDYD_ID = 1 AND LOT_ID_NUM = 219 PEN_ID HD_CNT ----------- ----------- 1 43 1 20 2 23 2 43 4 record(s) selected. + db2 SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID FROM LOT_LOC AS L1, LOT_LOC AS L2 WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM AND L1.FDYD_ID = L2.FDYD_ID AND L1.PEN_ID = L2.PEN_ID AND L1.TO_DATE = DATE('9999-12-31') AND L2.TO_DATE = DATE('9999-12-31') LOT_ID_NUM LOT_ID_NUM PEN_ID ----------- ----------- ----------- 0 record(s) selected. + db2 SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID FROM LOT_LOC AS L1, LOT_LOC AS L2 WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM AND L1.FDYD_ID = L2.FDYD_ID AND L1.PEN_ID = L2.PEN_ID LOT_ID_NUM LOT_ID_NUM PEN_ID ----------- ----------- ----------- 137 219 1 137 219 1 137 374 1 219 374 1 219 374 1 5 record(s) selected. + db2 SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L1.FROM_DATE, L1.TO_DATE FROM LOT_LOC AS L1, LOT_LOC AS L2 WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM AND L1.FDYD_ID = L2.FDYD_ID AND L1.PEN_ID = L2.PEN_ID AND L2.FROM_DATE <= L1.FROM_DATE AND L1.TO_DATE <= L2.TO_DATE UNION SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L1.FROM_DATE, L2.TO_DATE FROM LOT_LOC AS L1, LOT_LOC AS L2 WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM AND L1.FDYD_ID = L2.FDYD_ID AND L1.PEN_ID = L2.PEN_ID AND L1.FROM_DATE > L2.FROM_DATE AND L2.TO_DATE < L1.TO_DATE AND L1.FROM_DATE < L2.TO_DATE UNION SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L2.FROM_DATE, L1.TO_DATE FROM LOT_LOC AS L1, LOT_LOC AS L2 WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM AND L1.FDYD_ID = L2.FDYD_ID AND L1.PEN_ID = L2.PEN_ID AND L2.FROM_DATE > L1.FROM_DATE AND L1.TO_DATE < L2.TO_DATE AND L2.FROM_DATE < L1.TO_DATE UNION SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L2.FROM_DATE, L2.TO_DATE FROM LOT_LOC AS L1, LOT_LOC AS L2 WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM AND L1.FDYD_ID = L2.FDYD_ID AND L1.PEN_ID = L2.PEN_ID AND L2.FROM_DATE >= L1.FROM_DATE AND L2.TO_DATE <= L1.TO_DATE LOT_ID_NUM LOT_ID_NUM PEN_ID FROM_DATE TO_DATE ----------- ----------- ----------- ---------- ---------- 219 374 1 02/25/1998 03/01/1998 219 374 1 03/01/1998 03/14/1998 2 record(s) selected. + db2 SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, CASE WHEN L1.FROM_DATE > L2.FROM_DATE THEN L1.FROM_DATE ELSE L2.FROM_DATE END, CASE WHEN L1.TO_DATE > L2.TO_DATE THEN L2.TO_DATE ELSE L1.TO_DATE END FROM LOT_LOC AS L1, LOT_LOC AS L2 WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM AND L1.FDYD_ID = L2.FDYD_ID AND L1.PEN_ID = L2.PEN_ID AND (CASE WHEN L1.FROM_DATE > L2.FROM_DATE THEN L1.FROM_DATE ELSE L2.FROM_DATE END) < (CASE WHEN L1.TO_DATE > L2.TO_DATE THEN L2.TO_DATE ELSE L1.TO_DATE END) LOT_ID_NUM LOT_ID_NUM PEN_ID 4 5 ----------- ----------- ----------- ---------- ---------- 219 374 1 02/25/1998 03/01/1998 219 374 1 03/01/1998 03/14/1998 2 record(s) selected.