Re: SQL Challenge

CREATE TABLE ROB.FILEC AS (
SELECT 'D' AS STATUS, B.*
FROM FILEB B EXCEPTION JOIN FILEA A ON A.FIELDA=B.FIELDA AND
A.FIELDB=B.FIELDB
UNION ALL
SELECT 'I' AS STATUS, A.*
FROM FILEA A EXCEPTION JOIN FILEB B ON A.FIELDA=B.FIELDA AND
A.FIELDB=B.FIELDB
UNION ALL
SELECT 'C' AS STATUS, A.*
FROM FILEA A JOIN FILEB B ON A.FIELDA=B.FIELDA AND
A.FIELDB=B.FIELDB
WHERE A.FIELDC<>B.FIELDC OR A.FIELDD<>B.FIELDD OR A.FIELDE<>B.FIELDE
UNION ALL
SELECT 'X' AS STATUS, A.*
FROM FILEA A JOIN FILEB B ON A.FIELDA=B.FIELDA AND
A.FIELDB=B.FIELDB
WHERE A.FIELDC=B.FIELDC AND A.FIELDD=B.FIELDD AND A.FIELDE=B.FIELDE)
WITH DATA;

Proof:

CREATE TABLE ROB/FILEA (FIELDA CHAR (1 ) NOT NULL WITH DEFAULT,

FIELDB CHAR (1 ) NOT NULL WITH DEFAULT, FIELDC CHAR (1 ) NOT NULL
WITH DEFAULT, FIELDD CHAR (1 ) NOT NULL WITH DEFAULT, FIELDE CHAR
(1 ) NOT NULL WITH DEFAULT)
Table FILEA created in ROB.

INSERT INTO ROB/FILEA VALUES('A', 'A', 'A', 'A', 'A')

1 rows inserted in FILEA in ROB.

INSERT INTO ROB/FILEA VALUES('B', 'B', 'B', 'B', 'B')

1 rows inserted in FILEA in ROB.

INSERT INTO ROB/FILEA VALUES('C', 'C', 'C', 'C', 'C')

1 rows inserted in FILEA in ROB.

CREATE TABLE ROB/FILEB AS (SELECT * FROM ROB/FILEA) WITH DATA

Table FILEB created in ROB.

DELETE FROM ROB/FILEA WHERE FIELDA='C'

1 rows deleted from FILEA in ROB.

UPDATE FILEA SET FIELDC='1' WHERE FIELDA='B'

1 rows updated in FILEA in ROB.

INSERT INTO ROB/FILEA VALUES('D', 'D', 'D', 'D', 'D')

1 rows inserted in FILEA in ROB.

....+....1....+....2....+....3....+....4....+.
STATUS FIELDA FIELDB FIELDC FIELDD FIELDE
D C C C C C
I D D D D D
C B B 1 B B
X A A A A A
******** End of data ********

Rob Berendt