Merge statement in PL/SQL procedure question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Merge statement in PL/SQL procedure question

  1. #1
    Join Date
    Jul 2005
    Posts
    1

    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?

  2. #2
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    (edited: apologies, missed the error message halfway down your post)
    Last edited by WilliamR; 07-27-2005 at 02:30 PM.

  3. #3
    Join Date
    Dec 2000
    Posts
    138
    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

  4. #4
    Join Date
    Jan 2009
    Posts
    1
    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

  5. #5
    Join Date
    Jan 2009
    Posts
    11

    Use Right join

    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;

  6. #6
    Join Date
    Oct 2011
    Posts
    1

    Works in 11gR2

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width