1 Reply Latest reply on Apr 8, 2015 7:53 AM by Steven Hawkins

    'Recursive With' not working in queries of Transformation layer in VDB

    govindaraj s Novice

      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,

      PARENT_AREACHILD_AREA
      North AmericaUS
      USTexas
      TexasHarris
      North AmericaCanada
      CanadaAlberta
      AlbertaAlberta County

       

      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).

      PARENT_AREACHILD_AREA
      North AmericaUS
      USTexas
      TexasHarris
      North AmericaTexas
      North AmericaHarris
      USHarris
      North AmericaCanada
      CanadaAlberta
      AlbertaAlberta County
      North AmericaAlberta
      North AmericaAlberta County
      CanadaAlberta County

      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.