We are attempting to create a query that uses a Recursive Common Table Expression (CTE). This query works in native SQL within the database, but Clarity is generating an error.
Steps to Reproduce:
- Create a native SQL Query that uses a Recursive CTE
- Test the execution of the SQL Query in a database query tool
- Convert the SQL Query into Clarity NSQL Format
- Enter the Clarity NSQL Statement into a new Query in Studo
Expected Result: No error
Actual Result: "NPT-0109: The construct ... cannot be interpreted."
The error message generated may be misleading. The Recursive Common Table Expression (CTE) cannot be parsed with the Clarity NSQL parser and is not supported directly in a Clarity NSQL Query. The query can be created within a database view (using a user-defined naming convention beginning with 'Z_') and then within Clarity, create the NSQL as a simple select statement to read the records produced by the database view.
Keywords: CLARITYKB, how-to, configuration.