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

book

Article ID: 4350

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

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.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 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." 

Cause

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

Environment

CA PPM 15.9.1 with Jaspersoft 7.8

Resolution

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.

 

 

Attachments

1558715602694000004350_sktwi1f5rjvs16twb.png get_app