NSQL Query XOGS fail in v13.3

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Description:

XML Open Gateway (XOG) NSQL Query generates an error in v13.3 even though it works in v13.2 and earlier

Steps to Reproduce:

1. XOG out the Requirements by Risk query using the following XOG read file:
   

   <?xml version="1.0" encoding="UTF-8"?> 
   <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd"> 
    <Header version="8.0" action="read" objectType="contentPack" externalSource="NIKU"> 
    <!-- the contentType is used to determine which filter goes where --> 
    <args contentType="job_definition" name="order_by_1" value="code"/> 
    <args contentType="menu" name="order_by_1" value="code"/> 
    <args contentType="view" name="order_by_1" value="code"/> 
    <args contentType="process" name="order_by_1" value="code"/> 
    <args contentType="object" name="order_by_1" value="code"/> 
    </Header> 
    <QueryQuery> 
    <Filter name="code" criteria="EQUALS">rqrmnt.requirementsByRisk</Filter> 
    </QueryQuery> 
   </NikuDataBus>

2. Edit the output file
3. Change the name from "Requirements by Risk" to "Requirements by Risk 2"
4. Change the ID from "rqrmnt.requirementsByRisk " to "rqrmnt.requirementsByRisk _2"
5. Save your changes
6. Perform a XOG write action using your updated output file back into the same Clarity Server

Expected Result: The XOG write should be successful to create a new instance.

Actual Result: The XOG write errors out.

 <XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd">
 <Object type="contentPack"/> 
 <Status elapsedTime="1.593 seconds" state="FAILURE"/> 
 <Statistics failureRecords="1" insertedRecords="1" totalNumberOfRecords="1" updatedRecords="0"/> 
 <Records> 
 <Record> 
 <KeyInformation> 
 <column name="externalSource">NIKU</column> 
 <column name="source">customer</column> 
 <column name="code">rqrmnt.requirementsByRisk_</column> 
 </KeyInformation> 
 <ErrorInformation> 
 <Severity>FATAL</Severity> 
 		<Description>XOG-3052: Query record has not been inserted com.niku.union.persistence.nsql.NSQLSyntaxException: NSQL Syntax exception:
 	NSQL_SYNTAX_ERROR_DB_ERROR, Info: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00923: FROM keyword not found where expected 
&#xd; 
	SQL Text:&#xd;  
   SELECT RQP_REQUIREMENTS.RISK RISK, COUNT(RISK) COUNTSFROM RQP_REQUIREMENTSWHERE RQP_REQUIREMENTS.RELEASE_ID = ? AND 1=? and 1=1 GROUP BY RISK HAVING
   COUNT(RISK) &gt; 0 &#xd; 	 
 	---- more ----

Solution:

WORKAROUND:

The error is generated because when the XOG read action creates the output file, the contents of the NSQL Query does not have proper formatting.

Review the XOG output file and insert a space at the end of every line containing the NSQL Query code.

STATUS/RESOLUTION:

CLRT-73978

Resolved in Clarity 13.3.0 Generic Patch. Reference TEC605767.

Resolved in CA PPM 14.1  

Environment

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