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.

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


Article ID: 4350


Updated On:


Clarity PPM SaaS Clarity PPM On Premise


Domains where derived tables query start 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 which 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.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 created 

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


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


CA PPM 15.9.1 with Jaspersoft 7.8


Till the defect is fixed, please use the below options 

  • Continue to use Jaspersoft 7.1.3 
  • Please 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 fix is documented.




1558715602694000004350_sktwi1f5rjvs16twb.png get_app