Merge statement in PL/SQL procedure question

From: <dragos.toader_at_gmail.com>
Date: 26 Jul 2005 13:57:24 -0700
Message-ID: <1122411444.439675.266920@g14g2000cwa.googlegroups.com>

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));

SELECT * FROM employerTimeLine;

EMPLOYERID EFFECTIVE EXPIRY
---------- ---------------------- ----------------------
1000747 6/1/1996
1000505 6/1/1996

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));

SELECT * FROM employerTimeLine;

EMPLOYERID EFFECTIVE EXPIRY
---------- ---------------------- ----------------------
1000747 6/1/1996
1000505 6/1/1996

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));

SELECT * FROM employerTimeLine;

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

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));

SELECT * FROM employerTimeLine;

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

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? Received on Tue Jul 26 2005 - 15:57:24 CDT