Re: Tree Walking/Connect By

Michael J Dorey (scoec_at_westminster.ac.uk) wrote:
: I am using CONNECT BY to produce an organisational chart based on an employee
: details table. However, I also want to include some department info (from
: another table). I know that you cannot have a join *and* a connect by in
: the same select statement, but is there any way round this, so that data
: from another table can be incorporated??? I have tried creating a view
: which has the employee-department join in it, and then using CONNECT BY
: on the view, but I am *still* getting an error 'ORA-01437: cannot have join
: with CONNECT BY'. I always assumed that a view would always behave as
: a base table for further operations regardless of it's underlying logic,
: but obviously that is not the case. Does anyone else have any experience
: of using CONNECT BY on anything other than a single base table??

I don't think it can be done in a single SELECT. You could always use a procedural language (e.g. PL/SQL, Pro*C) to do the root query and then step through the results.

• Dan