Thanks for the prompt response.
The problem is of course that the actual career_history table has rather
more than 4 rows. I have pretty much decided that data cleansing is the way
to go.This is a third party app so changing constraints isn't an option. To
do this I have to identify all employee2s. It was an efficient SQL or PL/SQL
method that I was after.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Ed Prochak" <edprochak_at_magicinterface.com> wrote in message
news:3BB42635.F23B7484_at_magicinterface.com...
> Not all data is clean and not all problems have neat, simple solutions.
>
> Seems to me you need to decide whether
>
> to change the constraints on career_snapshot to allow for the possibility
of
> part-time work in more than one store
> OR
> to scrub the data, determining where employee 2 really worked during
months 7
> thru 11 (split the difference???)
> OR
> to nominate employee 2 for Sainthood (bi-location is an acceptable miracle
for
> Sainthood)
>
>
> Ed Prochak
> I've been doing a lot of scrubbing recently, so I may be a little cranky
8^)
>
>
> Niall Litchfield wrote:
>
> > I have a problem which I have have fictionalized below. Consider the
career
> > history of fictional employees as shown in the table career_history
(extract
> > below). this details the store at which each employee works and a
starting
> > and ending period when they were employed there. so employee 1 starts at
> > store 1 in period 01, works there until period 8 and then transfers to
store
> > 2 where he works for the rest of the year.
> >
> > If I wish to update the table career_snapshot_11 with his store at
period 11
> > I would issue
> >
> > update career_snapshot_11 s
> > set storeid = (select storeid from career_history h
> > where h.empno = s.empo
> > and s.period between h.start_period and h.end_period
> > and s.period = 11);
> >
> > leading to the first row shown in career_snapshot.
> >
> > unfortunately for employee2 the statement would fail with
> >
> > single-row subquery returns more than one row
> >
> > because employee 2 is apparently working at both stores in this time
period.
> >
> > So my problem is
> >
> > Given only career_history how can I find all employees who are shown as
> > working at different stores in the same period. We the of course have in
> > reality the problem of finding out which of the two rows is correct and
> > which is wrong, but that is a different story.
> >
> > career_history
> > storeid empno start_period end_period
> >
> > 1 1 01 08
> > 2 1 09 12
> > 2 2 01 11
> > 1 2 07 12
> >
> > career_snapshot_11
> > empno, storeid, period
> > 1 2 11
> > 2 <?> 11
> >
> > I have considered a number of different approaches to this, but really
can't
> > come up with a neat solution (which I am sure must exist).
> >
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
>
Received on Fri Sep 28 2001 - 02:39:33 CDT
