Using periods with left outer join

Database
N/A

Using periods with left outer join

Let's create two simple tables using the same ID column, some attributes (A_CDC and B_CDC) and a date period.

CREATE VOLATILE TABLE TEST_A
(
ID INTEGER NOT NULL
,A_CDC INTEGER NULL
,A_PRD PERIOD(DATE) NULL
) PRIMARY INDEX ( ID )
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE TEST_B
(
ID INTEGER NOT NULL
,B_CDC INTEGER NULL
,B_PRD PERIOD(DATE) NULL
) PRIMARY INDEX ( ID )
ON COMMIT PRESERVE ROWS;

INSERT INTO TEST_A (ID, A_CDC, A_PRD) VALUES (1, 100, PERIOD(DATE'2012-01-01', DATE'2012-06-01'));
INSERT INTO TEST_A (ID, A_CDC, A_PRD) VALUES (1, 200, PERIOD(DATE'2012-06-01', DATE'2012-08-01'));
INSERT INTO TEST_A (ID, A_CDC, A_PRD) VALUES (1, 300, PERIOD(DATE'2012-09-01', UNTIL_CHANGED ));

INSERT INTO TEST_B (ID, B_CDC, B_PRD) VALUES (1, 11, PERIOD(DATE'2012-04-01', DATE'2012-07-01'));
INSERT INTO TEST_B (ID, B_CDC, B_PRD) VALUES (1, 22, PERIOD(DATE'2012-08-01', DATE'2012-10-01'));
INSERT INTO TEST_B (ID, B_CDC, B_PRD) VALUES (1, 33, PERIOD(DATE'2012-11-01', UNTIL_CHANGED ));

I'd like to use TEST_A as driving table and create a set of records from 2012-01-01 using LEFT OUTER JOIN to join TEST_B.

SELECT
A.ID
,A.A_CDC
,B.B_CDC
,A_PRD P_INTERSECT COALESCE(B_PRD,A_PRD) AS PRD
FROM TEST_A A
LEFT JOIN TEST_B B ON A.ID = B.ID
WHERE PRD IS NOT NULL;

The SELECT above works well if I need only the intersecting periods.

 ID A_CDC B_CDC PRD
1 1 100 11 ('2012-04-01', '2012-06-01')
2 1 200 11 ('2012-06-01', '2012-07-01')
3 1 300 22 ('2012-09-01', '2012-10-01')
4 1 300 33 ('2012-11-01', '9999-12-31')

However, if I want to use the full timeline from TEST_A and fill B_CDC value from TEST_B if available, the query above is obviously not going to work.

The output I am looking for:

 ID A_CDC B_CDC PRD
1 1 100 <NULL> ('2012-01-01', '2012-04-01')
2 1 100 11 ('2012-04-01', '2012-06-01')
3 1 200 11 ('2012-06-01', '2012-07-01')
4 1 200 <NULL> ('2012-07-01', '2012-08-01')
5 1 300 22 ('2012-09-01', '2012-10-01')
6 1 300 <NULL> ('2012-10-01', '2012-11-01')
7 1 300 33 ('2012-11-01', '9999-12-31')

The COALESCE command helps when there is no match in the joined TEST_B table. Unfortunately, the P_INTERSECT cuts off the periods when they partially overlap. The next step would be something like that:

SELECT
A.ID
,A.A_CDC
,B.B_CDC
,A_PRD P_INTERSECT COALESCE(B_PRD,A_PRD) AS PRD
FROM TEST_A A
LEFT JOIN TEST_B B ON A.ID = B.ID
WHERE PRD IS NOT NULL

UNION

SELECT
A.ID
,A.A_CDC
,NULL
,A_PRD LDIFF COALESCE(B_PRD,A_PRD) AS PRD
FROM TEST_A A
LEFT JOIN TEST_B B ON A.ID = B.ID
WHERE PRD IS NOT NULL

UNION

SELECT
A.ID
,A.A_CDC
,NULL
,A_PRD RDIFF COALESCE(B_PRD,A_PRD) AS PRD
FROM TEST_A A
LEFT JOIN TEST_B B ON A.ID = B.ID
WHERE PRD IS NOT NULL;

The output is better but not good enough.

 ID A_CDC B_CDC PRD
1 1 100 <NULL> ('2012-01-01', '2012-04-01')
2 1 100 11 ('2012-04-01', '2012-06-01')
3 1 200 11 ('2012-06-01', '2012-07-01')
4 1 200 <NULL> ('2012-07-01', '2012-08-01')
5 1 300 22 ('2012-09-01', '2012-10-01')
6 1 300 <NULL> ('2012-09-01', '2012-11-01')
7 1 300 <NULL> ('2012-10-01', '9999-12-31')
8 1 300 33 ('2012-11-01', '9999-12-31')

As expected, there are overlapping periods. It can be improved using QUALIFY and/or ranking, although it's getting more and more complicated...

Is there a better way to do this?

Miklos