Merge statement in PL/SQL procedure question

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?

(edited: apologies, missed the error message halfway down your post)

Offtopic,I just saw the truncate table, I dont know the exact situation you are in, but did you check with global temporary tables.

Also you are using the full outer join, with inline views, I have had to deal with a similar situation with 9.2.0.3 and it still had problems inside procedures, I used row level security to solve it, metalink was of no help either. Try rls on employee_id and present.
The other way you can give a try is to have it as a view with a sys_context and se the context in your procedure.

Post your db version.

HTH
-dharma

Hi Dragos,

I am also facing a similar problem in Merge statement where I am using a Function parameter inside a sub-query. Looks like it is not able to expand the value of the parameter inside the sub query in the Merge statement.
Did you find any resolution for your problem?

Pankaj

Try this one....
I got the answer.......

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;
DBMS_OUTPUT.PUT_LINE('0');
FOR vI IN 0..months LOOP

MERGE INTO employerTimeLine etl
USING (SELECT future.employerId fut,
present.employerId pres
FROM (SELECT employerId
FROM coverageDates
WHERE add_months(to_date(lowerBound),vI) between effective and expiry
GROUP BY employerId) present
RIGHT 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.pres)
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.fut, add_months(lowerBound,vI+1));
END LOOP;
END;

2 years after the last post this may be already unnecessary, but just in case I want to say that everything works fine in 11gR2.
Seems like FULL OUTER JOIN was causing the problem.
In prior versions I experienced cases when FULL OUTER JOIN didn't work properly, though in different context.
So, hopefully, Oracle corrected something in processing FULL OUTER JOINs.