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

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

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.

Environment

Release: ESPCLA99000-12.1-Clarity-Extended Support Plus
Component: