NPT-0113: The dimension PROJECT has more than one key specified

book

Article ID: 33193

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL Clarity PPM SaaS - Application Clarity PPM On Premise

Issue/Introduction

Issue:

Trying to create a custom portlet to display several project KPIs in a grid portlet.  I need to UNION multiple SELECT statements in the NSQL of the portlet query, but I get a syntax error:

NPT-0113: The dimension PROJECT has more than one key specified.

I have attached the SQL statement that I wish to convert to NSQL.  I think the problem is that I’m using the same SELECT statement after the UNION 

I have attached the SQL statement that I started with and successfully run in Toad.  And I have attached the NSQL statement that I created from the SQL statement. The NSQL statement gives me an error when I try to Preview

 

Customer Example NSQL Statement using Union:

@SELECT   @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:i.id:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.code:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:lu.lookup_type:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:captions.name:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:captions.description:[email protected]
FROM [a list of table views or inline subqueries]
WHERE [table joins and filtering criteria]
AND i.id = @WHERE:PARAM:XML:INTEGER:/data/id/@[email protected]
UNION
@SELECT   @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:i.id:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.code:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:lu.lookup_type:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:captions.name:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:captions.description:[email protected]
FROM [a list of table views or inline subqueries]
WHERE [table joins and filtering criteria]
AND i.id = @WHERE:PARAM:XML:INTEGER:/data/id/@[email protected]

WHERE @[email protected]

 

Cause:

The error message is generated because of the NSQL constructs are used twice in the query.  The word 'PROJECT' in the error message refers to the dimension definition from the NSQL query.

Resolution:

 

NSQL adds SQL constructs to the end of the statement for automated filtering and other statements. This can create problems when you use UNION in NSQL. Use the @[email protected] in the outer select of an inline view that encapsulates the UNION statement. See below for an example of defining an inline subquery in the FROM statement to build a virtual table view to retrieve the values for the NSQL.

 

Correct Example NSQL Statement using Union:

@SELECT   @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:myUnionQry.id:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:myUnionQry.code:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:myUnionQry.lookup_type:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:myUnionQry.name:[email protected],
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:myUnionQry.description:[email protected]
FROM
(
SELECT [a list of attributes] FROM [a list of table views or inline subqueries] WHERE [joins and filter criteria] UNION
SELECT [a list of attributes]
FROM [a list of table views or inline subqueries]
WHERE [joins and filter criteria]
) myUnionQry
WHERE myUnionQry.id = @WHERE:PARAM:XML:INTEGER:/data/id/@[email protected] 
AND @[email protected]

 

Additional Information:

Reference the CA PPM Studio Development Guide: Studio NSQL Queries topic.

 

Environment

Release: ESPCLA99000-13.3-Clarity-Extended Support Plus
Component: