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

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

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

 

Error In Query Designer

Environment:
CA PPM 14.2 and above with Jaspersoft
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

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