I have two tables TASKS and TASK_PREDECESSORS

CREATE TABLE TASKS
(
ID NUMBER NOT NULL,
NAME VARCHAR2(200 BYTE) NOT NULL,
PARENT_ID NUMBER
);

ID NAME PARENT_ID
1 A
2 a 1
3 b 1


CREATE TABLE TASK_PREDECESSORS
(
PREDECESSOR_ID NUMBER NOT NULL,
SUCCESSOR_ID NUMBER NOT NULL
);

PREDECESSOR_ID SUCCESSOR_ID
2 3


I am looking for a query that returns all tasks in an oder that holds
the following:

(1) children before parents
(2) predecessors before successors


i.e. the data in the table should return [ a, b, A ] - it should also
generate an error or indicator
that detects a cycle.

I'm offering my apology ahead if this is a sheer waste of time for
others. May be, I am missing something really obvious here and I'm
still trying this.

Would really appreciate if anyone can offer a tip.

Thanks.

P.S. - This is not a homework. But yes, something I am working on..

On Fri, 17 Feb 2006 20:53:06 -0800, oracle_doc wrote:

>
> P.S. - This is not a homework. But yes, something I am working on..

select level,name
from tasks
connect by prior id = parent_id
start with parent_id is null;

It would be interesting to know how are you working if you didn't bother
to read any manuals. This is very elementary stuff that you could have
lerned by simply looking into SQL Reference manual. To save you the
trouble of painful and exhausting reading, allow me to direct you to
the following article:
http://www.oracle.com/technology/pro...ily/oct04.html



--
http://www.mgogala.com

Thank You, Mladen. I remember reading you from 1999 You're as
helpful as ever.

I am trying to do something a bit more complicated here than to just
build a hierarchical tree. I think I should have supplied more data
here. Sorry!!

Take a look at the TASK_PREDECESSORS table.
The records could be -
PREDECESSOR_ID SUCCESSOR_ID
2 3
3 4
5 3
5 6

Tasks -

ID NAME PARENT_ID
1 A
2 a 1
3 b 1
5 c 1

I want a resultset that would *not only* build the tree within TASKS
table -- but sort it
on PREDECESSOR - SUCCESSOR ID as well.

So, in the above example, I am looking for a resultset -

{A a c b}

I, of course, could do it in PL/SQL. The whole idea of putting this
question was to get an idea if this is doable in SQL only. Maybe, using
analytics? I don't know.

Again, thanks for pointing out to the manual. It's remained on my
bookmark (and a favourite weekend read, believe me or not) for over 10
years now

Mladen Gogala wrote:
> On Fri, 17 Feb 2006 20:53:06 -0800, oracle_doc wrote:

Actually, in the above example, I was wrong. The sorting on ID -
PARENT_ID should happen in the reverse order,

i.e.,
(1) children before parents (TASKS table)
(2) predecessors before successors (TASK_PREDECESSORS table)

So, the final dataset should look like -

{b c a A}

Is this doable through a single SQL?

"oracle_doc" <nilendu@nilendu.com> a écrit dans le message de news: 1140255476.221077.203740@f14g2000cwb.googlegroups. com...
| Actually, in the above example, I was wrong. The sorting on ID -
| PARENT_ID should happen in the reverse order,
|
| i.e.,
| (1) children before parents (TASKS table)
| (2) predecessors before successors (TASK_PREDECESSORS table)
|
| So, the final dataset should look like -
|
| {b c a A}
|
| Is this doable through a single SQL?
|

I don't understand your example:
- c (5) is precedessor of b (3), so shouldn't this be { c b ... }?
- a (2) is also predecessor of b (3), so shouldn't it be displayed before b?
- how do you handle multiple predecessors/successors?
- is this possible?
- is this possible to have the same id in different parts of your graph?
- what is the wanted result with many parents?
- ...

Finally, can you post a complete (and consistent) example?

Regards
Michel Cadot

Michel,

Thanks for pointing out. It's 3AM here and I should take it easy

The dataset I want is -

{c a b A}

Parent appears last, children appears before parent. Children of same
level are sorted on PREDECESSOR child first - SUCCESSOR child last.

On Sat, 18 Feb 2006 01:30:36 -0800, oracle_doc wrote:

> I want a resultset that would *not only* build the tree within TASKS
> table -- but sort it
> on PREDECESSOR - SUCCESSOR ID as well.


That is unnecessary information which results only
in an overhead. Data model is bad.

--
http://www.mgogala.com

"oracle_doc" <nilendu@nilendu.com> a écrit dans le message de news: 1140262430.649551.98840@g44g2000cwa.googlegroups.c om...
| Michel,
|
| Thanks for pointing out. It's 3AM here and I should take it easy
|
| The dataset I want is -
|
| {c a b A}
|
| Parent appears last, children appears before parent. Children of same
| level are sorted on PREDECESSOR child first - SUCCESSOR child last.
|

Why "c a b" and not "a c b"?
Still waiting for a complete example with multiple children, multiple parents, multiple chains and so on.
That is with all possible cases you can have.

Regards
Michel Cadot

On Sat, 18 Feb 2006 01:37:56 -0800, oracle_doc wrote:

> Is this doable through a single SQL?

You have a messed up data model. You have "predecessor" and "successor"
data in another table and you want to use connect by to compute them. That
contradicts the rules of good design and you have problems. Your problem
is a consequence of bad design. The sooner you get to grip with it, the
better.

--
http://www.mgogala.com

It's so easy to denounce people these days! Anyways, Thanks again
Mladen for your candid expert's view.

Let's accept that this is a bad data model and I put the successor_id
in the TASKS table.

CREATE TABLE TASKS
(
PREDECESSOR_ID NUMBER NOT NULL,
NAME VARCHAR2(200 BYTE) NOT NULL,
PARENT_ID NUMBER,
SUCCESSOR_ID NUMBER
);

PREDECESSOR_ID NAME PARENT_ID SUCCESSOR_ID
1 A
2 a 1
3
3 b 1
4
4 c 1



In other words, this table has two parent-child relationships.
PARENT_ID - PREDECESSOR_ID and PREDECESSOR_ID - SUCCESSOR_ID.

Let's also assume that there could be only one PREDECESSOR for one
SUCCESSOR; and there could be only one PARENT for one PREDECESSOR.

So, the question is - is this possible to use just a SELECT and get
data back in form so that -
(1) predecessors before parents
(2) predecessors before successors

i.e., for this table - the output data should be --

[a b c A]

And, again, if this is "bad design", "insulting Oracle", "trying to use
buffer hit ratio and therefore should go to hell" - I'm fine if you
don't write back. DBA community these days have become ultra-critical
of everything that doesn't follow the pattern. (sigh!)