Jem -
Here is the procedure along with the tables that it works from. My tree
is slightly different in that sub-nodes on each level are ordered so you
will need to adjust for that. I would like to see your final results as
I might have a need for that in the future.
- John Croft

CREATE TABLE TREE_TBL (
NODE_ID INTEGER NOT NULL,
NODE_PRIOR INTEGER DEFAULT 0 NOT NULL,
NODE_NEXT INTEGER DEFAULT 0 NOT NULL,
PARENT_NODE_ID INTEGER DEFAULT 0 NOT NULL
);

CREATE TABLE NODE_TBL (
NODE_ID INTEGER NOT NULL,
NODE_NAME CHAR(64) CHARACTER SET NONE NOT NULL
);

BEGIN
NODE_LEVEL = 0;
NODE_LEVEL_ORDER = 0;
current_node_id = 0;
parent_node_id = 0;
next_node_id = 0;
prior_node_id = 0;
leaf = 0;
SELECT MIN(NODE_ID)
FROM TREE_TBL
WHERE PARENT_NODE_ID = 0 AND
NODE_PRIOR = 0
INTO :STARTING_NODE_ID;
WHILE (:STARTING_NODE_ID IS NOT NULL)
DO
BEGIN
/* Select starting node */
SELECT t.node_id, t.node_name, tt.node_next
FROM node_tbl t LEFT OUTER JOIN TREE_TBL tt
ON t.node_id = tt.node_id
WHERE t.node_id = :STARTING_NODE_ID
INTO :node_id, :node_name, :next_node_id;
IF (leaf = 0) THEN
NODE_LEVEL = NODE_LEVEL + 1;
ELSE
leaf = 0;
NODE_LEVEL_ORDER = NODE_LEVEL_ORDER + 1;
SUSPEND;
/* Assign current node */
current_node_id = STARTING_NODE_ID;
IF (:node_id IS NOT NULL) THEN
BEGIN
SELECT min(c.node_id)
FROM TREE_TBL c
WHERE (c.parent_node_id = :STARTING_NODE_ID) AND
(c.node_prior = 0)
INTO :STARTING_NODE_ID;
IF (:STARTING_NODE_ID IS NOT NULL) THEN
BEGIN
leaf = 0;
parent_node_id = current_node_id;
prior_node_id = STARTING_NODE_ID;
END
ELSE
BEGIN
leaf = 1;
SELECT nc.node_id
FROM TREE_TBL nc
WHERE nc.parent_node_id = :parent_node_id AND
nc.node_id = :next_node_id
INTO :STARTING_NODE_ID;
IF (:STARTING_NODE_ID IS NOT NULL) THEN
BEGIN
prior_node_id = STARTING_NODE_ID;
END
ELSE
BEGIN
WHILE ((:STARTING_NODE_ID IS NULL) AND (NODE_LEVEL > 1))
DO
BEGIN
SELECT nn.parent_node_id, nn.node_next
FROM TREE_TBL nn
WHERE nn.node_id = :parent_node_id
INTO :parent_node_id, :next_node_id;
SELECT st.node_id
FROM TREE_TBL st
WHERE st.parent_node_id = :parent_node_id AND
st.node_id = :next_node_id
INTO :STARTING_NODE_ID;
NODE_LEVEL = NODE_LEVEL - 1;
IF (:STARTING_NODE_ID IS NOT NULL) THEN
prior_node_id = STARTING_NODE_ID;
END
END
END
END
END
END

Jem Rayfield wrote:

>John,
>
>Yes please. Im sure it would be a good starting point.
>
>
>Thanks alot
>Jem Rayfield
>Technical Consultant
>Deutsche Bank [/]
>CIB - IT Infrastructure
>Corporate and Investment Bank (CIB)
>London
>t: +44(20)754-51943
>
>
>
> John Croft
> <jcroft@compsolve To: ib-support@yahoogroups.com
> .com> cc:
> Subject: Re: [ib-support] Tree as adjaceny List conversion to nested set.
> 06/02/2003 14:47
> Please respond to
> ib-support
>
>
>
>
>
>
>I have a stored procedure that will traverse the tree and you should be
>able to modify it to write the nested set records. Let me know if you
>want it.
>John Croft
>
>Jem Rayfield wrote:
>
>>Hi,
>>
>>I have a tree structure stored as an adjacency list
>>
>>create table tree (
>> id integer,
>> pid integer,
>> name varchar( 10 )
>>)
>>
>>
>>I want to convert this Tree into a nested set using the following
>>data structure.
>>
>>create table nested_set (
>> name varchar( 10 ),
>> left integer,
>> right integer
>>)
>>
>>
>>Has anyone got a URL/example stored-procedure for this type of
>>conversion?
>>
>>Thanks for any pointers.
>>
>>Jem Rayfield
>>Technical Consultant
>>Deutsche Bank [/]
>>CIB - IT Infrastructure
>>Corporate and Investment Bank (CIB)
>>London
>>t: +44(20)754-51943
>>
>>
>>
>>--
>>
>>This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
>>
>>
>>
>>
>>To unsubscribe from this group, send an email to:
>>ib-support-unsubscribe@egroups.com
>>
>>
>>
>>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>>
>
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>
>
>
>
>--
>
>This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
>
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>