Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Location
    Charlotte, NC
    Posts
    6

    Question Unanswered: Using Cursors to find value changes

    Hi- I am trying to find a field effective date. For instance in the people table you'll have several entries for a user with effective_start_date and effective_end_dates. When a change to a user's marital status occurs, the person record gets end dated and a new one gets created. So for an average employee they could have 6-7 different entries in this table. What I need to do is find the marital status effective date. So, starting with the active record move backwards evaluating the marital_status to see when it changes from ('M' -married to 'S' -single) which would indicate the effective date of the marriage.

    This is what I'd envison.

    2 cursors, 1 starting at the current record and the other starting at the next latest record. Then, going backwards, evaluate the marital status-- when they are not equal it indicates a change and you can take the effective date of the person record.

    I am new to cursors and procedures and any help anyone can be would be GREATLY appreciated.

    Regards,
    Jessica

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would strongly suggest that you do this without using the cursors. Use the same logic, but do it as a compound SELECT statement instead of as nested cursors. The performance will almost always be several hundered times faster, and the corresponding load on the machine will be less too!

    -PatP

  3. #3
    Join Date
    Jun 2004
    Location
    Charlotte, NC
    Posts
    6
    Can you show an example of compounded select... the cursor idea that I came up with is below for a different table:

    DECLARE
    v_username VARCHAR2(30);
    v_runtime DATE;
    p_username VARCHAR2(30);
    p_runtime DATE;
    r_username VARCHAR2(30);
    r_runtime DATE;

    CURSOR c_runtimes IS
    SELECT USERNAME, RUN_TIME FROM IR_WM_XREF.IR_BLOX_RUNTIME order by RUN_TIME desc;

    CURSOR d_runtimes IS
    SELECT USERNAME, RUN_TIME FROM IR_WM_XREF.IR_BLOX_RUNTIME order by RUN_TIME desc;

    BEGIN
    Open c_runtimes;
    Open d_runtimes;

    Fetch d_runtimes into p_username, p_runtime; --advance the cursor once
    --LOOP
    Fetch c_runtimes into v_username, v_runtime;
    Fetch d_runtimes into p_username, p_runtime;
    --EXIT when d_runtimes%NOTFOUND; --d is ahead of c

    /* process data here */

    WHILE v_username = p_username LOOP
    Fetch c_runtimes into v_username, v_runtime;
    Fetch d_runtimes into p_username, p_runtime;
    EXIT when d_runtimes%NOTFOUND;

    --DBMS_OUTPUT.put_line (v_username || ' ' || v_runtime);

    END LOOP;


    r_username := v_username;
    r_runtime := v_runtime;

    DBMS_OUTPUT.put_line (v_username || ' ' || v_runtime);
    DBMS_OUTPUT.put_line (p_username || ' ' || p_runtime);
    DBMS_OUTPUT.put_line (r_username || ' ' || r_runtime); -- this is result

    CLOSE c_runtimes;
    END;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm not at all sure what your PL/SQL does right now, except that I'm pretty sure it doesn't do what you want!

    Here is an example using pure SQL:
    Code:
    SQL> select * from people;
    
    USERNAME   STATUS EFF_DATE
    ---------- ------ -----------
    FRED       S      01-JAN-2001
    FRED       S      01-JAN-2002
    FRED       M      01-JAN-2003
    FRED       M      01-JAN-2004
    GINGER     S      01-JAN-2001
    
    SQL> select p1.username, p1.status, min(p1.eff_date) min_eff_date
      2    from people p1
      3   where not exists (select null
      4                       from people p2
      5                      where p2.username = p1.username
      6                        and p2.status != p1.status
      7                        and p2.eff_date > p1.eff_date)
      8  group by p1.username, p1.status  ;
    
    USERNAME   STATUS MIN_EFF_DAT
    --------- ------ -----------
    FRED       M      01-JAN-2003
    GINGER     S      01-JAN-2001
    (I hope nobody saw the horrendously complicated version of this I posted and then quickly edited!)
    Last edited by andrewst; 06-29-04 at 12:35.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please forgive the size of the example, but:
    Code:
    CREATE TABLE tMarital (
       personId		INT		NOT NULL
    ,  asof			DATETIME	NOT NULL
       CONSTRAINT XPKtMarital
          PRIMARY KEY (personId, asof)
    ,  status		CHAR(1)		NOT NULL
       CONSTRAINT XCK01tMarital CHECK (status IN ('M', 'S'))
       )
    
    INSERT tMarital (personId, asof, status) SELECT 1, '1924-03-01', 'S'
    INSERT tMarital (personId, asof, status) SELECT 2, '1925-06-01', 'S'
    INSERT tMarital (personId, asof, status) SELECT 1, '1948-03-01', 'M'
    INSERT tMarital (personId, asof, status) SELECT 2, '1949-06-01', 'M'
    INSERT tMarital (personId, asof, status) SELECT 2, '1950-03-01', 'S'
    INSERT tMarital (personId, asof, status) SELECT 2, '1958-06-01', 'M'
    INSERT tMarital (personId, asof, status) SELECT 2, '1970-03-01', 'S'
    INSERT tMarital (personId, asof, status) SELECT 1, '1969-11-01', 'S'
    
    SELECT a.*
       FROM tMarital AS a
       LEFT OUTER JOIN tMarital AS b
          ON (b.personId = a.personId)
       WHERE  b.asof = (SELECT Max(c.asof)
          FROM tMarital AS c
          WHERE  c.personId = a.personId
             AND c.asof < a.asof)
          AND a.status <> b.status
    
    SELECT * FROM tMarital AS a
       ORDER BY a.personId, a.asof
    -PatP

  6. #6
    Join Date
    Jun 2004
    Location
    Charlotte, NC
    Posts
    6
    I was able to build a function to do this, But I agree that you're SQL Tony is much simpler. Would there be substantial run time differences between the function or the SQL... also, I guess you have to take into consideration that we need to do this for a couple fields, then maybe for reusability it would make sense to keep it as a function--

    CREATE OR REPLACE Function Get_MarStatus_date (
    i_person_id VARCHAR2
    ) Return DATE As

    a_marstatus VARCHAR2(30);
    a_effectiveDate DATE;
    b_marstatus VARCHAR2(30);
    b_effectiveDate DATE;
    --r_marstatus VARCHAR2(30); --result
    r_effectiveDate DATE;

    CURSOR cursor_a IS
    --SELECT USERNAME, RUN_TIME FROM IR_WM_XREF.IR_BLOX_RUNTIME order by RUN_TIME desc;
    SELECT MARITAL_STATUS, EFFECTIVE_START_DATE FROM PER_ALL_PEOPLE_F WHERE PERSON_ID = i_person_id
    ORDER BY EFFECTIVE_START_DATE DESC;

    CURSOR cursor_b IS
    --SELECT USERNAME, RUN_TIME FROM IR_WM_XREF.IR_BLOX_RUNTIME order by RUN_TIME desc;
    SELECT MARITAL_STATUS, EFFECTIVE_START_DATE FROM PER_ALL_PEOPLE_F WHERE PERSON_ID = i_person_id
    ORDER BY EFFECTIVE_START_DATE DESC;

    BEGIN
    Open cursor_a;
    Open cursor_b;


    Fetch cursor_a into a_marstatus, a_effectiveDate;
    Fetch cursor_b into b_marstatus, b_effectiveDate;
    Fetch cursor_b into b_marstatus, b_effectiveDate; --advance the cursor a second time

    /* process data here */

    WHILE a_marstatus = b_marstatus LOOP
    Fetch cursor_a into a_marstatus, a_effectiveDate;
    Fetch cursor_b into b_marstatus, b_effectiveDate;
    EXIT when cursor_b%NOTFOUND;
    END LOOP;

    --a_marstatus := v_username;
    r_effectiveDate := a_effectiveDate;

    RETURN r_effectiveDate;

    CLOSE cursor_a;
    CLOSE cursor_b;

    End WEBM.Get_MarStatus_date;
    /

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If your function works and the performance is acceptable to you, and you find a function preferable, then by all means go with the function. I'm pretty sure the SQL will be faster, but maybe not by so much that a user would notice significantly. However, I don't understand your point about "reusability", since you will have to write a separate copy of the function for each table. How is that so different from writing a separate select statement per table? Maybe I'm missing something!

Posting Permissions

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