Clarity: Using a Recursive CTE in an NSQL Query generates the Error NPT-0109: The construct cannot be interpreted

Document ID : KB000049812
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

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:

  1. Create a native SQL Query that uses a Recursive CTE

  2. Test the execution of the SQL Query in a database query tool

  3. Convert the SQL Query into Clarity NSQL Format

  4. Enter the Clarity NSQL Statement into a new Query in Studo

Expected Result: No error

Actual Result: "NPT-0109: The construct ... cannot be interpreted."

Solution:

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.