ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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

book

Article ID: 49812

calendar_today

Updated On:

Products

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:
Powered by Wolken Software