Re: SQL Problem

Thanks for you response. This sort of approach was what I was trying. Unfortunately a combination of tiredness (and the actual problem having a few other wrinkles) mean't that I couldn't get this to work sensibly on multimillion row tables. Thanks for the input though it is a nice elegant approach.

This and the pure sql answer also illustrate quite well that there are many ways to skin a cat in Oracle.

Do any of the guys who teach sql etc on this list ever ask the bright sparks to solve the given problem in two or more different ways, and to compare and contrast each approach.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"D R Patterson" <patterd1_at_home.com> wrote in message
news:3BB88D9B.695C10CB_at_home.com...


> Niall, how about the following:

>

> set serveroutput on Format TRUNC;

>

> DECLARE

>   v_empno       career_history.empno%TYPE;

>   CURSOR C_multi1 IS

>     SELECT empno, count( storeid) storecount

>     FROM    career_history

>     HAVING  count(storeid) > 1

>     GROUP BY empno;

>   CURSOR C_multi2 IS

>     SELECT      storeid,

>                 start_period,

>                 end_period

>     FROM        career_history

>     WHERE       empno = v_empno

>     ORDER BY    start_period;

>   v_prior_end   career_history.end_period%TYPE;

>   v_prior_store career_history.storeid%TYPE;

> BEGIN

>   DBMS_Output.Put_Line( 'Start of list of problem employees.');

>   -- this gives me a list of the employees who have worked for

>   -- two different stores, or multiple periods in one store.

>   FOR v_empl_multi in C_multi1 LOOP

>     -- save the employee number

>     v_empno := v_empl_multi.empno;

>     -- open the second cursor to walk through the entries and

>     -- see if there is an overlap

>     v_prior_end := -1;

>     FOR v_empl_hist in C_multi2 LOOP

>       -- look at each empl/store row

>       IF v_prior_end > 0 -- if this not the first entry for this empl

>          AND v_prior_end >= v_empl_hist.start_period THEN

>           -- starting before left prior store!

>           DBMS_Output.Put_line( 'Overlap with employee ' ||

>              To_Char( v_empno));

>           DBMS_Output.Put_line( '  Worked at store ' ||

>              To_Char( v_prior_store ) || ' until period ' ||

>              To_Char( v_prior_end ) );

>           DBMS_Output.Put_line( '  Started at store ' ||

>              To_Char( v_empl_hist.storeid ) || ' in period ' ||

>              To_Char( v_empl_hist.start_period ) );

>       END IF;

>     -- keep values for next entry

>     v_prior_end   := v_empl_hist.end_period;

>     v_prior_store := v_empl_hist.storeid;

>     END LOOP;

>     -- DBMS_Output.Put_Line( v_empl_multi.empno );

>   END LOOP;

> END;

> /

>

> It has two cursors -- the first will select only the employees with

multiple


> rows of experience.  The second will look at the career history rows for

each


> identified employee and flag the ones with overlapping work history.

>

> I think it would be fairly efficient if the right indices are present.

>

> Dave Patterson

> patterd1_at_home.com

>