SQL Tile - not able to reference CTE name in itself when using recursion
I am trying to do a fairly simple recursion cte to navigate an employee hierarchy and assign an integer "level" to each employee based on their place in the org. I am using a SQL tile in a Magic ETL. I am getting an error from Domo "Table Not Found: hierarchytable". This I believe is referencing the FROM hierarchytable as eh portion in the recursive section of the cte. Can anyone help me figure out why I am getting this error and how to make this work?
My full code:
Best Answer
-
I'm going to venture a guess and say you expecting too much from the SQL tile. When it was introduced to us, we were told it would have limits. Having the SQL tile reference itself isn't something I'd expect it to do.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **1
Answers
-
I'm going to venture a guess and say you expecting too much from the SQL tile. When it was introduced to us, we were told it would have limits. Having the SQL tile reference itself isn't something I'd expect it to do.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **1 -
You can use Domo’s Magic ETL to simulate recursion if your hierarchy isn't too deep or large.
- Filter rows where ManagerID IS NULL (top-level).
- Join the result to the hierarchy where ManagerID = EmployeeID.
Repeat the above as multiple steps (one per level).Append each level's result together. You’ll be limited by the maximum depth you hardcode in ETL.
Or, if you control source data, process outside Domo. And import the result into Domo.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **1 -
Thanks, I wasn't sure what the limits were. The supported functions documentation I've seen doesn't mention CTEs at all, though "some" are supported.
0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 666 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive