Clarity PPM

Expand all | Collapse all

Issue: Creating a query in a Clarity with oracle database via XOG API

Jump to Best Answer
  • 1.  Issue: Creating a query in a Clarity with oracle database via XOG API

    Posted 04-23-2019 08:02 AM

    I am trying to create a clarity query via XOG API:

     

    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_contentPack.xsd">
    <Header action="write" externalSource="NIKU"
    objectType="contentPack" version="8.0"/>
    <contentPack update="true">
    <queries update="true">
    <query code="ABC" source="customer">
    <nls languageCode="en" name="xyz"/>
    <nsql dbId="Niku" dbVendor="all">
    Select @Select:DIM:USER_DEF:IMPLIED:PRJ:srm_projects.unique_name:CODE@
    ,@Select:DIM:USER_DEF:IMPLIED:PRJ1:srm_projects.ID:ID@
    ,@Select:srm_projects.NAME:NAME@
    ,@Select:srm_projects.LAST_UPDATED_DATE:LAST_UPDATED_DATE_FILTER@
    ,@Select:to_char(srm_projects.LAST_UPDATED_DATE, 'HH24:MI:SS.FF3'):LAST_UPDATED_DATE@
    ,@Select:to_char(srm_projects.CREATED_DATE, 'HH24:MI:SS.FF3'):CREATED_DATE@
    FROM srm_projects
    WHERE @FILTER@
    </nsql>
    </query>
    </queries>
    </contentPack>
    </NikuDataBus>

     

    But getting the following error after posting:

     

    Error - Error:XOG-3052: Query record has not been inserted com.niku.union.persistence.nsql.NSQLSyntaxException: NSQL Syntax exception: NSQL_SYNTAX_ERROR_UNKNOWN_CONSTRUCT, Info: @Select:to_char(srm_projects.LAST_UPDATED_DATE, 'HH24:MI:SS.FF3') AS VARCHAR):LAST_UPDATED_DATE@ at com.niku.union.persistence.nsql.NSQLStatementSet.checkRemaingNSQLConstructs(NSQLStatementSet.java:762) at com.niku.union.persistence.nsql.NSQLStatementSet.parseNSQL(NSQLStatementSet.java:741) at com.niku.union.persistence.nsql.NSQLStatementSet.(NSQLStatementSet.java:228) at com.niku.union.persistence.nsql.NSQLStatementSet.(NSQLStatementSet.java:132) at com.niku.union.persistence.nsql.NSQLQuery.initialize(NSQLQuery.java:268) at com.niku.union.persistence.nsql.NSQLQuery.setNSQL(NSQLQuery.java:792) at com.niku.union.persistence.nsql.NSQLQuery.(NSQLQuery.java:328) at com.niku.union.persistence.nsql.NSQLController.createQuery(NSQLController.java:173) at com.niku.union.persistence.nsql.NSQLController.createQuery(NSQLController.java:184) at com.niku.npt.xblhandlers.GGNSQLHandler.xogStoreQuery(GGNSQLHandler.java:768) at com.niku.npt.xblhandlers.GGNSQLHandler.postProcess(GGNSQLHandler.java:124) at com.niku.xql2.XQLVisitor.postProcess(XQLVisitor.java:1417) at com.niku.union.xml.dom.DOMWalker.postProcess(DOMWalker.java:210) at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:94) at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:51) at com.niku.xql2.handlers.LoopHandler.processObject(LoopHandler.java:388) at com.niku.xql2.handlers.LoopHandler.processSet(LoopHandler.java:259) at com.niku.xql2.handlers.LoopHandler.preProcess(LoopHandler.java:216) at com.niku.xql2.XQLVisitor.preProcess(XQLVisitor.java:1383) at com.niku.union.xml.dom.DOMWalker.preProcess(DOMWalker.java:194) at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:74) at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92) at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:51) at com.niku.xql2.handlers.LoopHandler.processObject(LoopHandler.java:388) at com.niku.xql2.handlers.LoopHandler.processSet(LoopHandler.java:259) at com.niku.xql2.handlers.LoopHandler.preProcess(LoopHandler.java:216) at com.niku.xql2.XQLVisitor.preProcess(XQLVisitor.java:1383) at com.niku.union.xml.dom.DOMWalker.preProcess(DOMWalker.java:194) at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:74) at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:51) at com.niku.xql2.handlers.LoopHandler.processObject(LoopHandler.java:388) at com.niku.xql2.handlers.LoopHandler.processSet(LoopHandler.java:259) at com.niku.xql2.handlers.LoopHandler.preProcess(LoopHandler.java:216) at com.niku.xql2.XQLVisitor.preProcess(XQLVisitor.java:1383) at com.niku.union.xml.dom.DOMWalker.preProcess(DOMWalker.java:194) at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:74) at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:51) at com.niku.xql2.handlers.LoopHandler.processObject(LoopHandler.java:388) at com.niku.xql2.handlers.LoopHandler.processSet(LoopHandler.java:259) at com.niku.xql2.handlers.LoopHandler.preProcess(LoopHandler.java:216) at com.niku.xql2.XQLVisitor.preProcess(XQLVisitor.java:1383) at com.niku.union.xml.dom.DOMWalker.preProcess(DOMWalker.java:194) at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:74) at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:51) at com.niku.xql2.handlers.TryHandler.preProcess(TryHandler.java:54) at com.niku.xql2.XQLVisitor.preProcess(XQLVisitor.java:1383) at com.

     

     

    It seems that the error is with the NSQL. 

    I have used the 'to_char' for converting datetime value to a specified format.

     

    Can anyone provide me with a correct solution?



  • 2.  Re: Issue: Creating a query in a Clarity with oracle database via XOG API
    Best Answer

    Posted 04-23-2019 08:58 AM

    Take that NSQL, paste it into a query in the application - you will get the same error, so its nothing to do with XOG.

     

    The problem is just with your NSQL, these lines are all wrong

    ,@Select:DIM:USER_DEF:IMPLIED:PRJ1:srm_projects.ID:ID@

    ,@Select:srm_projects.NAME:NAME@
    ,@Select:srm_projects.LAST_UPDATED_DATE:LAST_UPDATED_DATE_FILTER@
    ,@Select:to_char(srm_projects.LAST_UPDATED_DATE, 'HH24:MI:SS.FF3'):LAST_UPDATED_DATE@
    ,@Select:to_char(srm_projects.CREATED_DATE, 'HH24:MI:SS.FF3'):CREATED_DATE@

     

     

    they should be more like;

    ,@Select:DIM_PROP:USER_DEF:IMPLIED:PRJ:srm_projects.ID:ID@
    ,@Select:DIM_PROP:USER_DEF:IMPLIED:PRJ:srm_projects.NAME:NAME@
    ,@Select:DIM_PROP:USER_DEF:IMPLIED:PRJ:srm_projects.LAST_UPDATED_DATE:LAST_UPDATED_DATE_FILTER@
    ,@Select:DIM_PROP:USER_DEF:IMPLIED:PRJ:to_char(srm_projects.LAST_UPDATED_DATE, 'HH\:24\:MI\:SS.FF3'):LAST_UPDATED_DATE@
    ,@Select:DIM_PROP:USER_DEF:IMPLIED:PRJ:to_char(srm_projects.CREATED_DATE, 'HH24\:MI\:SS.FF3'):CREATED_DATE@

     

    (you have just got confused about how you define query dimensions and properties, perhaps getting confused with the syntax we need to use for creating lookups as well)

     

    Also, personally I would NOT cast the date/time fields to strings either ; leave them as dates and handle any conversion in the downstream usage of the query.