We have requirements to show up hierarchical data. The data comes from a single entity or table in the data source like say AREA_DETAIL table which has columns like PARENT_AREA, CHILD_AREA where PARENT_AREA will contain the parent assets and CHILD_AREA will contain the child assets.

For example data in the source is like,

So we have a requirement to get the every other asset connected to every other asset related, like (check out the highlighted parts, which should be present in the view).

To achieve this, we had used 'WITH' statement in SQL whcih connects to the WITH block recursively and use to get the data as in the second table format.

Using the same query did not work out in Teiid Transformation. So right now, we have hard code for the hierarchy levels available. But we would need a solution to get the second table format like hiearrchy definition as per the data available in the first table.

Question:

How do we achieve this and do we have to do anythiny to make the recursive 'WITH' statement work?

Environment:

We are using Teiid 8.7

When we are searching, we got an solution (i,e) the Recursive With is working in 8.10 (WITH Clause - Teiid 8.10 - Project Documentation Editor) onward, but we unable to upgrade our Teiid version now, so is there any workaround for "Recursive With" in Teiid 8.7 and also when we are trying to create Recursive With in Teiid designer view SQLtransformation, it shows some error like the "with table Group doesn't exist"

Thanks

Govindaraj s.