SQL Query starting with WITH shows invalid in Query Editor in Domain Designer under Advanced Reporting

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Domains, where derived tables query starts with WITH Clause in Jaspersoft 7.8, are failing with "The Query is not valid. A derived table query must start with Select and contain at least 4 words or attributes placeholder".

Steps to Reproduce 

1. Create a custom domain or use any out of box domains provided
2. Edit the domain and create a derived table 
3. In the query section, write a SQL query that starts with WITH clause (sample query below) 

WITH all_categories AS (
        SELECT r.rim_category_key, COUNT(*)::numeric AS total_risks
        FROM dwh_rim_risk r
        GROUP BY  r.rim_category_key
    ),  category_inv AS (
        SELECT r.investment_key, r.rim_category_key, rln.rim_category, COUNT(*)::numeric AS total_risks
        FROM dwh_rim_risk r
             JOIN dwh_rim_risk_ln rln ON  rln.risk_key = r.risk_key
                                      AND rln.language_code = 'en'
        GROUP BY r.investment_key, r.rim_category_key, rln.rim_category
     )
SELECT i.investment_name,
       category_inv.rim_category,
       ((category_inv.total_risks / all_categories.total_risks)*100)::numeric  AS cat_percentage
FROM   dwh_inv_investment i
       JOIN dwh_inv_investment_ln iln ON iln.investment_key = i.investment_key
                                      AND iln.language_code = 'en'
       JOIN category_inv ON category_inv.investment_key = i.investment_key
       JOIN all_categories ON all_categories.rim_category_key = category_inv.rim_category_key

Expected Result: The derived table should validate successfully and be created. 

Actual Result: The derived table is not created and you see the error "The Query is not valid. A derived table query must start with Select and contain at least 4 words or attributes placeholder." 

Environment

Clarity 15.9.1 and 15.9.2 with Jaspersoft 7.8

Cause

This is a defect of Tibco Jaspersoft # and Broadcom Defect # DE60610 is currently in review with Broadcom and the Tibco engineering team.

Resolution

Workarounds:

• Continue to use Jaspersoft 7.1.3 
  OR
• Alter your queries and start with SELECT * FROM TABLENAME in your domains derived tables. 

Additional Information

Note: This impacts only writing queries at Domain Designer and doesn't impact writing queries using Jaspersoft Studio. There was another defect for queries not getting validated in Jaspersoft Studio and information along with the fix is found at: Custom reports fail with error "6632" post Jaspersoft 7.8 upgrade.