Clarity: SQL Query Starting with WITH shows invalid in Query Editor in Domain Designer under CA PPM Advance Reporting.

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Query is not valid. A derived table query must start with Select and contain at least 4 words or attributes placeholder.

 

<Please see attached file for image>

Cause

When you write a query using below as example or doesn't start with  SELECT * FROM TABLENAME. 

 

WITH

subquery_name

AS

(the aggregation SQL statement)

SELECT

(query naming subquery_name);

 

This is a known bug with Jaspersoft and the bug number is JS-27554

Environment

CA PPM 14.2 and above with Jaspersoft

Resolution

Please alter your queries and start with SELECT * FROM TABLENAME. 

Additional Information

Note: This impacts only writing queries at Domain Designer and doesn't impact writing queries using Jaspersoft Studio