Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
SELECT statementUse the SELECT statement to retrieve values from a database or from an SPL or collection variable. A SELECT operation is called a query.
Hierarchical ClauseThe Hierarchical clause sets the conditions for recursive queries on a table object in which a hierarchy of parent-child dependencies exists among the rows. SELECT statements that include this clause are called hierarchical queries.
Dependency patterns that are not a simple graphYou can run a recursive hierarchical query on a data set that includes complex dependencies, such as multiple root nodes, or multiple parent nodes for child nodes. However, cycling might occur and more records might be returned, based on hierarchical data tree structures.

Dependency patterns that are not a simple graph

You can run a recursive hierarchical query on a data set that includes complex dependencies, such as multiple root nodes, or multiple parent nodes for child nodes. However, cycling might occur and more records might be returned, based on hierarchical data tree structures.

The CONNECT BY clause cannot analyze data sets that include cycles where some child nodes are identified as ancestors of their parent node. If your data set includes cycles, the cycles might be artifacts of rows that contain invalid data.

If the data set that the self-referential table describes includes more than one hierarchy, include the START WITH clause with a condition that is only true for the root of the hierarchy that you want the recursive query to return. Run a separate query on the table for each hierarchy, using a different START WITH clause to specify the root in each query.