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:
but I'm not really familiar with Progress yet so I was hoping someone might be able to help verify or improve this.
Thanks
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:
So searching for "Part01" should traverse the data such that: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
Currently I have patched this together:Part01
....Part02
........Part04
............Part05
................Part08
................Part09
............Part07
........Part05
....Part03
........Part04
............Part05
................Part08
................Part09
............Part07
........Part06
............Part10
............Part11
Code:
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