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

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

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:Investment_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.code:Investment_code@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:lu.lookup_type:LookupType@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:captions.name:LookupName@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:captions.description:LookupDescription@
FROM [a list of table views or inline subqueries]
WHERE [table joins and filtering criteria]
AND i.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@
UNION
@SELECT   @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:i.id:Investment_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.code:Investment_code@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:lu.lookup_type:LookupType@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:captions.name:LookupName@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:captions.description:LookupDescription@
FROM [a list of table views or inline subqueries]
WHERE [table joins and filtering criteria]
AND i.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@

WHERE @FILTER@

 

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 @SELECT@ 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:Investment_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:myUnionQry.code:Investment_code@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:myUnionQry.lookup_type:LookupType@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:myUnionQry.name:LookupName@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:myUnionQry.description:LookupDescription@
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/@value@ 
AND @FILTER@

 

Additional Information:

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