Re: Newbie SQL question

In article <7tj19h$9s3$1_at_nnrp1.deja.com>,   Ed Prochak <prochak_at_my-deja.com> wrote:
> In article <7tik47$vjq$1_at_nnrp1.deja.com>,
> steve_billingsley_at_my-deja.com wrote:
> > Thank you to anyone who can find an answer to this .....
> >
> > any user can be defined as a supervisor using their user_id (with no
> > checking to make sure two people aren't each others supervisor
> > creating a never ending loop)
> >
> >Any hints on how to do such a thing?
 

>
> Use the CONNECT BY clause. A variation of this should work for you:
>
> select user_id, name
> from emp
> start with name='Joe'
> connect by supervisor = prior user_id;
>

Thanks Ed,
This works wonderfully, however my problem stated above exists in the database. Apparently our database is structured to require a supervisor, so the top guy is supervised by our cfo who is supervised by the top guy, in a nice loop :)

The SQL I am using looks like this

select distinct user_id, name from emp
where user_id <> user
start with user_id = user
connect by supervisor = prior user_id
order by name

Oracle seems to choke on this. - I would prefer not to make a special case for the two employees. Any ideas on how to fix this loop?

Thanks,
steve

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 08 1999 - 00:00:00 CDT