I have a data source where due to it's hierarchical nature that I need to flatten it & output as a list (with hierarchy level no. being just another column) my approach is as follows.
Say we have 3 levels of hierarchy, I would set the first view, view_l1 being the first list, add a hierarchy level no. column, & of course, from the source, the last column is an [Array]...
My second view, view_l2 will be a flattened view_l1 where the output is everything of that level & I removed everything from the first level, add a hierarchy level no. column, & the last column again is an [Array]...
My third view, view_l3 will be a flattened view_l2 ... and so on, no last array column of course.
Then I UNION the 3 of them together to get a long list.
Looking at the "Tree view", it seems each branch has it's own source, although it is from the same source. Looking at the execution trace, it does seem that each view does it's own query to get the source of data to reach it's own level. Is it possible to have the UNION where all the views are executed from only 1 query to the source? Or is there a better way than UNION?
Many thanks in advance.