search cancel

Need help to run an NSQL query (with xml filter criteria) to troubleshoot a performance issue

book

Article ID: 189280

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

We are using an integration tool uses NSQL to retrieve user information.

In our prior version (15.3), it seems the select process worked as expected.

In our new version (15.7.1), the select process is not qualifying a single user - instead it is returning all users.


I need to run this NSQL:

SELECT   @Select:DIM:USER_DEF:IMPLIED:ASSIGNMENT:R.ID:[email protected]

,        @Select:DIM_PROP:USER_DEF:IMPLIED:ASSIGNMENT:R.USER_ID:[email protected]

,        @Select:DIM_PROP:USER_DEF:IMPLIED:ASSIGNMENT:R.UNIQUE_NAME:[email protected]

,        @Select:DIM_PROP:USER_DEF:IMPLIED:ASSIGNMENT:R.FULL_NAME:[email protected]

,        @Select:DIM_PROP:USER_DEF:IMPLIED:ASSIGNMENT:U.USER_NAME:[email protected]

FROM     SRM_RESOURCES R LEFT OUTER JOIN CMN_SEC_USERS U ON R.USER_ID = U.ID

WHERE    @[email protected]

and apply this filter xml to it:

<ns2:Envelope xmlns:ns3="http://www.niku.com/xog/Query" xmlns:ns2="http://schemas.xmlsoap.org/soap/envelope/">

<ns2:Header>

<Auth>

<SessionID>my_session</SessionID>

</Auth>

</ns2:Header>

<ns2:Body>

<ns3:Query>

<ns3:Filter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="caPpmGetResourceFilter">

<ns3:unique_name_id_in>user_name</ns3:unique_name_id_in>

</ns3:Filter>

<ns3:Slice>

<ns3:Number>17844</ns3:Number>

<ns3:Size>2</ns3:Size>

</ns3:Slice>

<ns3:Sort>

<ns3:Column>

<ns3:Name>last_updated_date</ns3:Name>

<ns3:Direction>desc</ns3:Direction>

</ns3:Column>

</ns3:Sort>

<ns3:Code>Tasktop_GetResources</ns3:Code>

</ns3:Query>

</ns2:Body>

</ns2:Envelope>

Is it possible to do this?

Environment

Release : 15.7.1

Component : CA PPM XML OPEN GATEWAY (XOG)

Resolution

In the FIlter section customer was using unique_name_id_in to filter for specific user but it needed to be unique_name_in as per xsd of the query.