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:
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.