Skip to main content
  • EN

Hello Safers. Hope all is well.

Im wanting to use the InlineQuerier to do a WITH RECURSIVE select.

Trying to make a path from a tree stored in a relational model. Very much like this example: https://academy.vertabelo.com/blog/do-it-in-sql-recursive-tree-traversal/

Unfortunately I found that WITH support is not in version 3.8.2 of sqlite (which looks like what FMEs is using) WITH support was added with 3.8.3. Are there any plans to upgrade with future releases of FME? Or some way to patch it. Any other suggestions or workarounds? I guess I could write to a different database that supports WITH and read back. However, I was trying stay all with file based tables. Thanks in advance.

Hi @rwhittington, there is an existing idea to have better keep up with SQLite updates, please check out Keep Up with SQLITE Updates. I suggest you up-vote on the idea and if possible, add a comment regarding your use case (i.e.- to be able to use WITH in queries).

Can confirm that even FME 2018 Beta b18175 seems to be on SQLite version 3.8.2

There is an idea post somewhere to support actual graph databases like Neo4j and Stardog, looking at the SQL statement to traverse a graph makes my brain hurt :-)

 

 

Thanks for the comments everyone. Yea @bruceharold any time I think of solution using recursion makes my head hurts as well. and yet there is a lot of hierarchical data stored in relational tables because sql is very fast. Oracle specific hierarchical queries help; if you are on that platform. I like the power that the InlineQuerier (sqlite cache) provides for constructing complex relationships in a workflow. I'm sure we could solve this with lists and/or adjacent features which also make my head hurt. The Aggregator can construct hierarchies for geometry with parent/child ids so I might try creating some dummy geometry to see if that approach is easier. Thanks and good eve from Texas.

Reply


Community Stats

30,191
Posts
114,596
Replies
37,854
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.