Re: Merge statement in PL/SQL procedure question

dragos.toader_at_gmail.com schrieb:
> 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?
>

I suppose, you should open a TAR. The main problem in your code is FULL OUTER JOIN (maybe in combination with inline views) . For example , if you replace FULL OUTER JOIN with either LEFT OUTER JOIN or RIGHT OUTER JOIN, procedure can be executed without error ( despite the results are wrong ;-). But FULL OUTER JOIN produces that error ( i've tested it on 10gR2 as well 9iR2 ). Merge statement can be correctly parsed , so it is most likely a bug. And for you there are few choices - get support from Oracle or rewrite your sql.

Best regards

Maxim Received on Wed Jul 27 2005 - 16:33:32 CDT