CTE or Recursive Query for Hierarchical Data (Employee / BOM)

I would like to get insights on retrieving hierarchical data (e.g., employee reporting structure or BOM/Sub-BOM hierarchy) using CTE queries via ODBC/ADO.NET or recursive logic.

From my understanding, SuiteQL does not support CTEs or recursive queries, so I am exploring alternative ways to achieve this. I noticed some colleagues have implemented similar solutions and would like to learn from your experience.

Specifically:

• How did you implement hierarchical data retrieval using ODBC/ADO.Net or SuiteQL?
• Are there any workarounds or best practices for recursive queries in NetSuite?
• Any references to existing SuiteAnswers articles or SuiteIdeas enhancements that address this would be very helpful.