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

Article Id: 49812
Status: Published
Updated On: 14-02-2018 08:05
Legacy Id: TEC567732
Products:
CLARITY PPM FOR ITG , CA Project & Portfolio Management , CLARITY PPM FEDERAL , CA Project & Portfolio Management SAAS , CA Identity Manager , CA Identity Governance , CA Identity Portal , STARTER PACK-CLARITY PPM
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: