Mimer SQL will support the standard SQL WITH-clause syntax. This includes recursive queries with support for hierarchical ordering and cycle detection. 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 if the data 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 support these important constructs.
