Posted by Bart op de grote markt on 08/31/07 21:53

On 31 aug, 18:20, Shiju Samuel <shiju.sam...@gmail.com> wrote:
> Please post table structure. Also let us know what you want to update.
>
> -
> Shiju Samuel

Since I am not at work now, I can't copy the whole stored procedure
that I had till now; but concerting the table structure, it is like
this
#TEMP_TABLE with columns ID, PARENT_ID, SEQUENCE, DESCRIPTION, P_ID_1,
P,_ID_2, P_ID_3,...,P_ID_10
The records in the #TEMP_TABLE are first imported from a databasetable
(only columns ID, PARENT_ID, SEQUENCE, DESCRIPTION) and they represent
a tree-like structure with the root-nodes having a PARENT_ID-value of
NULL. Now I want the stored procedure to return every node with all
of its parents until the root node.(which will be in P_ID_1) E.g. 1 >
1A > 1A1 will be the result for record [1A1 | 1A | 3 | description of
1A1]

So the first step is importing the data into #TEMP_TABLE.
The 2nd step is to put the ID's of all records that have NULL as
PARENT_ID into their P_ID_1 column.
The 3rd step is to put ID's of all records that have a PARENT_ID that
is in (SELECT P_ID_2 FROM #TEMP_TABLE b) , which is a result op step
2, into P_ID_2. We put their parent_ID then into P_ID_1.
The 4th step is the SQL from my first post. It is easy to fill in
P_ID_2 and P_ID_3, but for P_ID_1 I need a subselect, and it doesn't
seem to work like I explained in my first post.


Grtz,

Bart

[Back to original message]