AnsweredAssumed Answered

NSQL Query -- Not returning data as expected

Question asked by mtognetti on Feb 5, 2018
Latest reply on Feb 6, 2018 by Dave_3.0

Good Day Community –

 

I have a new question as I venture on my path of learning who to integrate data from a third party system to PPM and back again. We have successfully made the connection from our third party system (Cherwell) and can create an Idea.  (Yea!!)  We are now looking to read data back for writing to Cherwell.  Based on reading and answers to other threads I believe the way to go is to create a query and utilize the WSDL to read the limited data back.  This also allows us to use a custom field in order to filter the query.

 

I have written the query in T-SQL and converted it to NSQL. It works as I expect in T-SQL but not quite the same in NSQL.  I am sure the issue is in my conversion.   At this time I only pull back six (6) fields.  If these fields three (3) of them could be NULL.  These are the NOTEID, DIARY_ENTRY, and the LATESTENTRYDATE.  I also have a input parameter which will be a numeric and represents the Cherwell SR number.  In my t-sql I can run the query with any positive match of the field PH_Cherwell_ID and return all six fields, even if there is a null in the NoteID; however, in the NSQL the same is not true.  I receive an error (not defined)  but it says “Exact”  when I run it from my test portlet page (see attached)

 

 

Here are my queries:

 

T-SQL

SELECT

       n.id as NOTEID 

       , i.code

       , SRNBR.ph_cherwell_sr_id

       , ide.id

       , n.diary_entry

       , max(n.entry_date) as LatestEntryDate

          --nsql select

          --@SELECT:DIM:USER_DEF:IMPLIED:IDEA:N.ID:NOTEID@,

       --@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:I.CODE:CODE@,

       --@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:SRNBR.PH_CHERWELL_SR_ID:CHERWELL_SR@,

       --@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:IDE.ID:ID@,

       --@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:N.DIARY_ENTRY:DIARYENTRY@,

       --@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:MAX(N.ENTRY_DATE):ENTRYDATE@

FROM   NIKU.INV_INVESTMENTS i JOIN NIKU.INV_IDEAS IDE

   ON i.ID = IDE.ID

LEFT JOIN [niku].[ODF_CA_IDEA_NOTES] N   

   INNER JOIN (

                           SELECT

                              MAX(N.last_updated_date) AS MaxLastUpdateNote

                              , N.ODF_CNCRT_PARENT_ID

                           FROM niku.odf_ca_idea_notes N

                           GROUP BY N.ODF_CNCRT_PARENT_ID

                                  ) AS UniqueNote

ON uniquenote.ODF_CNCRT_PARENT_ID = N.odf_parent_id

   AND uniquenote.maxLastUpdateNote = N.last_updated_date

   ON IDE.id = N.ODF_CNCRT_PARENT_ID

LEFT JOIN NIKU.ODF_CA_IDEA AS SRNbr

ON IDE.id = srnbr.id

 

       --tsql where

WHERE I.ODF_OBJECT_CODE='IDEA'

          AND SRNBR.PARTITION_CODE = 'IST'

       AND (SRNBR.PH_CHERWELL_SR_ID IS NOT NULL

           OR SRNBR.PH_CHERWELL_SR_ID IS NULL)

       --TEST IDEAS

          AND SRNBR.ph_cherwell_sr_id IN (123469,123460,123459,123459,123458,123458,123457,123456,111112,111111,111113)

       --nsql where

--WHERE

--     I.ODF_OBJECT_CODE='IDEA'

--       AND (@WHERE:PARAM:USER_DEF:STRING:SR@ = SRNBR.PH_CHERWELL_SR_ID

--           OR @WHERE:PARAM:USER_DEF:STRING:SR@ IS NULL)

--       AND @FILTER@

 

   

GROUP BY

     N.ID,

     I.CODE,

     IDE.ID,

     N.DIARY_ENTRY,

     SRNBR.PH_CHERWELL_SR_ID

 

--ONLY FOR TSQL

ORDER BY CODE ASC

 

 

NSQL:

 

SELECT @SELECT:DIM:USER_DEF:IMPLIED:IDEA:N.ID:NOTEID@,

       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:I.CODE:CODE@,

       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:SRNBR.PH_CHERWELL_SR_ID:CHERWELL_SR@,

       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:IDE.ID:ID@,

       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:N.DIARY_ENTRY:DIARYENTRY@,

       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:MAX(N.ENTRY_DATE):ENTRYDATE@

FROM   NIKU.INV_INVESTMENTS i JOIN NIKU.INV_IDEAS IDE

   ON i.ID = IDE.ID

LEFT JOIN [niku].[ODF_CA_IDEA_NOTES] N   

   INNER JOIN (

SELECT

   MAX(N.last_updated_date) AS MaxLastUpdateNote

   , N.ODF_CNCRT_PARENT_ID

FROM niku.odf_ca_idea_notes N

GROUP BY N.ODF_CNCRT_PARENT_ID

      ) AS UniqueNote

ON uniquenote.ODF_CNCRT_PARENT_ID = N.odf_parent_id

   AND uniquenote.maxLastUpdateNote = N.last_updated_date

   ON IDE.id = N.ODF_CNCRT_PARENT_ID

LEFT JOIN NIKU.ODF_CA_IDEA AS SRNbr

ON IDE.id = srnbr.id WHERE I.ODF_OBJECT_CODE='IDEA'

       AND (@WHERE:PARAM:USER_DEF:STRING:SR@ = SRNBR.PH_CHERWELL_SR_ID

            OR @WHERE:PARAM:USER_DEF:STRING:SR@ IS NULL)

       AND @FILTER@

GROUP BY

      N.ID,

      I.CODE,

      IDE.ID,

    N.DIARY_ENTRY,

      SRNBR.PH_CHERWELL_SR_ID

 

Thank you in advance for you help and guidance.

Attachments

Outcomes