In Mimer SQL 11 support for the standard SQL WITH-clause syntax, including recursive queries with hierarchical ordering and cycle detection, will be available. A recursive WITH-clause gives the possibility to search and retrieve hierarchical relationships with an arbitrary depth. It is also great for generating data and number series. This functionality is unique for Mimer SQL.
For example, a query can be used to list all components in a parts explosion problem. Each part is made of subparts which are in turn made of smaller subparts etc. It is, for example possible to see the cost by getting the accumulated price of all component of an object. Or, it is possible to retrieve the parts and show the hierarchical structure. Two search orders are specifically supported. SQL has a SEARCH clause to specify BREADTH first traversal and the second one is DEPTH first traversal.
In some cases there may be cycles in the hierarchy. For example, assume you have stored the call sequence of routines in a computer program. These form a hierarchy starting at the main entry point. The main programs calls a number of underlying routines and so on. However, many computer languages support recursion, which means that a routine calls itself or a routine that has already been called higher up in the call sequence. If a hierarchical traversal is performed it would never terminate when it hits the first recursive call sequence. Instead, the SQL construction CYCLE helps both to locate and continue on from these places. Mimer SQL also has an option to give an error if a cycle is detected in data that is not supposed to contain cycles. Many database vendors have support for WITH-clause. The group is smaller when RECURSIVE functionality is needed. However, when it comes to standard support for SEARCH and CYCLE clause only Mimer SQL and one other vendor provide support for these important constructs.
