Using Cursors to find value changes

jherron · #1 (**permalink**) 06-29-04, 08:40

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

Pat Phelan · #2 (**permalink**) 06-29-04, 09:18

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

jherron · #3 (**permalink**) 06-29-04, 09:38

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;

andrewst · #4 (**permalink**) 06-29-04, 11:30

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

Pat Phelan · #5 (**permalink**) 06-29-04, 11:34

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

jherron · #6 (**permalink**) 06-29-04, 15:43

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

andrewst · #7 (**permalink**) 06-29-04, 17:36

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech