Building a complicated tree from criteria on its leaves

Hi Harald,

In our company, we have several similar use cases where we need custom trees (or a specific information from a tree).

I am not sure if this use case comparable to yours, but it´s maybe a good example: We have certain products that are UL certified. If we add alternative Manufacturer Parts to components, we need to inform UL about the change. However, it’s difficult for users to see whether a specific part is used in an UL-certified product. Some parts, such as a simple resistor, are used virtually everywhere. People would have to click through many BOM levels and Part references, just to find the few that are relevant

For this use case, we only need information about the top-level parts that represent the certified products. We solved this use case with a recursive SQL CTE query that traverses the levels and filters the necessary data ("=which non-EOL and UL certified products use this resistor?"). Bonus: The query is connected to the MCO/ECO, so the required additional step to inform UL is automatically added to the MCO if we have affected products.

The example represents a bottom-up approach. Of course, it can also be used the other way around.

Your question is a bit similar to this one: link
You might notice that also other users recommend SQL as solution for custom tree related queries.