Recursive progrss query for parent-child tree structure.

[at45]

Hi all

I'm looking to write a query which will query a part table for a multi-level report. Any part can have any number of sub-parts, which in turn can have their own sub-parts. The table is built like that below:

Part.........Sub-Part
Part01......Part02
Part01......Part03
Part02......Part04
Part02......Part05
Part03......Part06
Part03......Part04
Part04......Part05
Part04......Part07
Part05......Part08
Part05......Part09
Part06......Part10
Part06......Part11
Part12......Part13
Part12......Part14
Part13......Part15
Part13......Part16

So searching for "Part01" should traverse the data such that:

Part01
....Part02
........Part04
............Part05
................Part08
................Part09
............Part07
........Part05
....Part03
........Part04
............Part05
................Part08
................Part09
............Part07
........Part06
............Part10
............Part11

Currently I have patched this together:

WITH tempTable(Part, Subpart, Iteration) AS
(
SELECT Part, Subpart, 0
FROM partTable WHERE Part = [QueryString]
UNION ALLSELECT a.Part, b.Subpart, a.Iteration + 1
FROM temp_table AS a, part_table AS b
WHERE a.Subpart = b.Part
)

SELECT Subpart
FROM tempTable

but I'm not really familiar with Progress yet so I was hoping someone might be able to help verify or improve this.

Thanks

 

[mrobles]

This procedure use a table called part
It has 2 fields: Padre and Hijo

First I set the first element of the root and with this I begin the search.
Try it and then change the first instruction using 'part02' and see the result.
You can change the output file to locate it in a diferent path.
Sorry for my english.

DEF VAR inicial AS CHAR INITIAL 'part01'.
DEF VAR espacios AS INT.
DEF VAR txt AS CHAR FORMAT 'x(50)'.
OUTPUT TO c:\salida.txt.
txt = inicial.
DISPLAY txt WITH NO-LABELS STREAM-IO NO-BOX.
RUN busca(inicial).
OUTPUT CLOSE.

PROCEDURE busca:
DEF INPUT PARAMETER inicial AS CHAR.
FOR EACH part NO-LOCK WHERE
part.padre = inicial.
espacios = espacios + 2.
txt = FILL(' ', espacios) + part.hijo.
DISPLAY txt WITH NO-LABELS STREAM-IO NO-BOX.
RUN busca(part.hijo).
espacios = espacios - 2.
END.
END.

 

[KrisM]

You may want to change your procedure a little bit, to ensure it will work correctly:

PROCEDURE busca:
DEF INPUT PARAMETER inicial AS CHAR.
define buffer lbpart for part.
FOR EACH lbpart NO-LOCK WHERE
lbpart.padre = inicial.
espacios = espacios + 2.
txt = FILL(' ', espacios) + lbpart.hijo.
DISPLAY txt WITH NO-LABELS STREAM-IO NO-BOX.
RUN busca(lbpart.hijo).
espacios = espacios - 2.
END.
END.