Hi,

I've run into a problem with the merge statement.

Setup:
Each employer may have one or more members. Each member
has one or more date intervals.

I'm trying to coalesce a set of member date intervals into
a timeline for each employer.

Here's the sample data

DROP TABLE coverageDates;

CREATE TABLE coverageDates
(employerId NUMBER(8,0) NOT NULL,
memberId NUMBER(8,0) NOT NULL,
effective DATE NOT NULL,
expiry DATE NOT NULL);

INSERT INTO coverageDates(EMPLOYERID,MEMBERID,EFFECTIVE,EXPIRY)
VALUES(1000505,2990,'1-JUN-1996','31-JUL-1996');

INSERT INTO coverageDates(EMPLOYERID,MEMBERID,EFFECTIVE,EXPIRY)
VALUES(1000505,2990,'1-SEP-1996','30-SEP-1996');

INSERT INTO coverageDates(EMPLOYERID,MEMBERID,EFFECTIVE,EXPIRY)
VALUES(1000747,336,'1-JUN-1996','30-JUN-1996');

INSERT INTO coverageDates(EMPLOYERID,MEMBERID,EFFECTIVE,EXPIRY)
VALUES(1000747,336,'1-JUN-1996','31-JUL-1996');

INSERT INTO coverageDates(EMPLOYERID,MEMBERID,EFFECTIVE,EXPIRY)
VALUES(1000747,337,'1-SEP-1996','30-SEP-1996');

INSERT INTO coverageDates(EMPLOYERID,MEMBERID,EFFECTIVE,EXPIRY)
VALUES(1000771,1131,'1-SEP-1996','30-SEP-1996');

DROP TABLE employerTimeLine;

CREATE TABLE employerTimeLine
(employerId NUMBER(8,0) NOT NULL,
effective DATE NOT NULL,
expiry DATE NULL);

COMMIT;

/*
I've created a procedure to coalesce all member coverage
intervals into an employer timeline. This involves collapsing
many overlapping time intervals (I've used one month as the base
unit) into one. The procedure compiles.
*/

CREATE OR REPLACE
PROCEDURE buildEmployerTimeLine IS
vI NUMBER(1);
months NUMBER(1);
lowerBound DATE;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE employerTimeLine';

SELECT trunc(add_months(min(effective),-1),'month'),
months_between(
trunc(max(expiry),'month'),
trunc(add_months(min(effective),-1),'month'))
INTO lowerBound,
months
FROM coverageDates;

FOR vI IN 0..months LOOP
MERGE INTO employerTimeLine etl
USING (SELECT future.employerId future,
present.employerId present
FROM (SELECT employerId
FROM coverageDates
WHERE add_months(to_date(lowerBound),vI) between
effective and expiry
GROUP BY employerId) present
FULL OUTER JOIN
(SELECT employerId
FROM coverageDates
WHERE add_months(to_date(lowerBound),vI+1)
between effective and expiry
GROUP BY employerId) future
ON present.employerId=future.employerId
GROUP BY present.employerId,future.employerId
HAVING present.employerId is null OR
future.employerId is null) x
ON (etl.employerId=x.present)
WHEN matched THEN
UPDATE SET etl.expiry=nvl(etl.expiry,
last_day(
add_months(lowerBound,vI)))
WHEN not matched THEN
INSERT (etl.employerId,etl.effective)
VALUES (x.future, add_months(lowerBound,vI+1));
END LOOP;
END;
/

COMMIT;

BEGIN buildEmployerTimeLine; END;
/

/*
Here's the error. I do not know what the cause is.
The merge statement starts at line 17.

-- OUTPUT
-- -------------------------------------------------------------
-- 1:53:56 PM ORA-00923: FROM keyword not found where expected
-- 1:53:56 PM ORA-06512: at "PIA_ADMIN.BUILDEMPLOYERTIMELINE", line 17
-- 1:53:56 PM ORA-06512: at line 1

Doing the steps manually works.
*/

SELECT trunc(add_months(min(effective),-1),'month') lowerBound,
months_between(
trunc(max(expiry),'month'),
trunc(add_months(min(effective),-1),'month')) months
FROM coverageDates;

-- LOWERBOUND MONTHS
-- ---------------------- ---------------------------------------
-- 5/1/1996 4

MERGE INTO employerTimeLine etl
USING (SELECT future.employerId future,
present.employerId present
FROM (SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),0) between
effective and expiry
GROUP BY employerId) present
FULL OUTER JOIN
(SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),0+1) between
effective and expiry
GROUP BY employerId) future
ON present.employerId=future.employerId
GROUP BY present.employerId,future.employerId
HAVING present.employerId is null OR
future.employerId is null) x
ON (etl.employerId=x.present)
WHEN matched THEN
UPDATE SET etl.expiry=NVL(etl.expiry,
last_day(add_months('01-May-1996',0)))
WHEN not matched THEN
INSERT (etl.employerId,
etl.effective)
VALUES (x.future, add_months('01-May-1996',0+1));

MERGE INTO employerTimeLine etl
USING (SELECT future.employerId future,
present.employerId present
FROM (SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),1) between
effective and expiry
GROUP BY employerId) present
FULL OUTER JOIN
(SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),1+1) between
effective and expiry
GROUP BY employerId) future
ON present.employerId=future.employerId
GROUP BY present.employerId,
future.employerId
HAVING present.employerId is null OR
future.employerId is null) x
ON (etl.employerId=x.present)
WHEN matched THEN
UPDATE SET etl.expiry=NVL(etl.expiry,
last_day(add_months('01-May-1996',1)))
WHEN not matched THEN
INSERT (etl.employerId,etl.effective)
VALUES (x.future, add_months('01-May-1996',1+1));

MERGE INTO employerTimeLine etl
USING (SELECT future.employerId future,
present.employerId present
FROM (SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),2) between
effective and expiry
GROUP BY employerId) present
FULL OUTER JOIN
(SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),2+1) between
effective and expiry
GROUP BY employerId) future
ON present.employerId=future.employerId
GROUP BY present.employerId,
future.employerId
HAVING present.employerId is null OR
future.employerId is null) x
ON (etl.employerId=x.present)
WHEN matched THEN
UPDATE SET etl.expiry=NVL(etl.expiry,
last_day(add_months('01-May-1996',2)))
WHEN not matched THEN
INSERT (etl.employerId,etl.effective)
VALUES (x.future, add_months('01-May-1996',2+1));

MERGE INTO employerTimeLine etl
USING (SELECT future.employerId future,
present.employerId present
FROM (SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),3) between
effective and expiry
GROUP BY employerId) present
FULL OUTER JOIN
(SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),3+1) between
effective and expiry
GROUP BY employerId) future
ON present.employerId=future.employerId
GROUP BY present.employerId,
future.employerId
HAVING present.employerId is null OR
future.employerId is null) x
ON (etl.employerId=x.present)
WHEN matched THEN
UPDATE SET etl.expiry=NVL(etl.expiry,
last_day(add_months('01-May-1996',3)))
WHEN not matched THEN
INSERT (etl.employerId,etl.effective)
VALUES (x.future, add_months('01-May-1996',3+1));

MERGE INTO employerTimeLine etl
USING (SELECT future.employerId future,
present.employerId present
FROM (SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),4) between
effective and expiry
GROUP BY employerId) present
FULL OUTER JOIN
(SELECT employerId
FROM coverageDates
WHERE add_months(to_date('01-May-1996'),4+1) between
effective and expiry
GROUP BY employerId) future
ON present.employerId=future.employerId
GROUP BY present.employerId,
future.employerId
HAVING present.employerId is null OR
future.employerId is null) x
ON (etl.employerId=x.present)
WHEN matched THEN
UPDATE SET etl.expiry=NVL(etl.expiry,
last_day(add_months('01-May-1996',4)))
WHEN not matched THEN
INSERT (etl.employerId,etl.effective)
VALUES (x.future, add_months('01-May-1996',4+1));

SELECT * FROM employerTimeLine;

-- EMPLOYERID EFFECTIVE EXPIRY
-- ---------- ---------------------- ----------------------
-- 1000747 6/1/1996 7/31/1996
-- 1000505 6/1/1996 7/31/1996
-- 1000771 9/1/1996 9/30/1996
-- 1000747 9/1/1996 9/30/1996
-- 1000505 9/1/1996 9/30/1996

Why?