recursive select 2

[Thorsten3]

I'd like to represent a hierarchy in a table like

id name parent1 aaa null
2 bbb 1
3 ccc 1
4 ddd 3
is it possible to select reucursivly?
For example for id 4 I would like a result like this:
aaa-ccc-ddd
For id 3 like this:
aaa-ccc

 

[r937]

not in ansi sql, as far as i know -- maybe some recent version of standard sql has introduced recursion, but that matters little if no actual databases out in the real world support it

oracle is an exception, you can do it in oracle using START WITH and CONNECT BY syntax


rudy

 

[Thorsten3]

thanks rudy!

I'm using db2 at the moment. I suppose db2 can not do it!?

I will use oracle however in the near future, how would the select command actually look like for the example above?

thorsten

 

[karluk]

A hierarchical query in Oracle would look something like

select * from your_table
start with id = {identify top level row(s) here}
connect by prior id = parent1;

 

[HugoBerg]

browsing through the forum I saw that you may indeed use recursive select with DB2: thread220-479915

Haven't tried it myself, but I will sure do soon.

 

[dnoeth]

There is recursion in DB2, just look for WITH.

Dieter