|
Home > Archive > Other Oracle database topics > July 2005 > Merge statement in PL/SQL procedure question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Merge statement in PL/SQL procedure question
|
|
| dragos.toader@gmail.com 2005-07-26, 8:23 pm |
| 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(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
VALUES(1000505,2990,
'1-JUN-1996','31-JUL-1996');
INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
VALUES(1000505,2990,
'1-SEP-1996','30-SEP-1996');
INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
VALUES(1000747,336,'
1-JUN-1996','30-JUN-1996');
INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
VALUES(1000747,336,'
1-JUN-1996','31-JUL-1996');
INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
VALUES(1000747,337,'
1-SEP-1996','30-SEP-1996');
INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,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 buildEmployerTimeLin
e 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),'m
onth'),
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(l
owerBound),vI) between
effective and expiry
GROUP BY employerId) present
FULL OUTER JOIN
(SELECT employerId
FROM coverageDates
WHERE add_months(to_date(l
owerBound),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(lowerBoun
d,vI)))
WHEN not matched THEN
INSERT (etl.employerId,etl.effective)
VALUES (x.future, add_months(lowerBoun
d,vI+1));
END LOOP;
END;
/
COMMIT;
BEGIN buildEmployerTimeLin
e; 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. BUILDEMPLOYERTIMELIN
E", 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),'m
onth'),
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?
| |
| Maxim Demenko 2005-07-27, 8:23 pm |
| dragos.toader@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(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
> VALUES(1000505,2990,
'1-JUN-1996','31-JUL-1996');
>
> INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
> VALUES(1000505,2990,
'1-SEP-1996','30-SEP-1996');
>
> INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
> VALUES(1000747,336,'
1-JUN-1996','30-JUN-1996');
>
> INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
> VALUES(1000747,336,'
1-JUN-1996','31-JUL-1996');
>
> INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,EXPIRY)
> VALUES(1000747,337,'
1-SEP-1996','30-SEP-1996');
>
> INSERT INTO coverageDates(EMPLOY
ERID,MEMBERID,EFFECT
IVE,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 buildEmployerTimeLin
e 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),'m
onth'),
> 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(l
owerBound),vI) between
> effective and expiry
> GROUP BY employerId) present
> FULL OUTER JOIN
> (SELECT employerId
> FROM coverageDates
> WHERE add_months(to_date(l
owerBound),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(lowerBoun
d,vI)))
> WHEN not matched THEN
> INSERT (etl.employerId,etl.effective)
> VALUES (x.future, add_months(lowerBoun
d,vI+1));
> END LOOP;
> END;
> /
>
> COMMIT;
>
> BEGIN buildEmployerTimeLin
e; 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. BUILDEMPLOYERTIMELIN
E", 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),'m
onth'),
> 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
|
|
|
|
|