Clarity

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.

If your T-sql is working fine put it inside a wrapper and work from there on

SELECT @SELECT:DIM:USER_DEF:IMPLIED:X.NOTEID:NOTEID@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:X.CODE:CODE@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:X.PH_CHERWELL_SR_ID:CHERWELL_SR@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:X.ID:ID@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:X.DIARY_ENTRY:DIARYENTRY@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:X.LatestEntryDate:ENTRYDATE@
FROM

(
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
) X

Another suggestion if you haven't tried already, from within Clarity, click the "Preview" button on the Query to get Clarity's interpreted SQL, and try using that in SQL Studio to see if it still behaves as you'd expect. If it doesn't, compare the queries to see what got changed.

L.Elias,

Thank you for your input.  While I had looked at the query in preview to ensure it would run, I did not compare it to my own translated SQL  What I find is that the two SQL are the same with the exception of the @Filter@ expanded syntax.  This syntax add quite a few lines to the Where clause and this is what I think is causing the data return that I am seeing.  The extra lines are:

       AND 1=1
AND N.ID IN (NULL)
AND N.ID = @noteid
AND N.ID >= @noteid_from
AND N.ID <= @noteid_to
AND UPPER(I.CODE) LIKE UPPER(@code_wildcard) ESCAPE '\'
AND I.CODE IN (NULL)
AND I.CODE = @code
AND I.CODE >= @code_from
AND I.CODE <= @code_to
AND UPPER(SRNBR.PH_CHERWELL_SR_ID) LIKE UPPER(@cherwell_sr_wildcard) ESCAPE '\'
AND SRNBR.PH_CHERWELL_SR_ID IN (NULL)
AND SRNBR.PH_CHERWELL_SR_ID = @cherwell_sr
AND SRNBR.PH_CHERWELL_SR_ID >= @cherwell_sr_from
AND SRNBR.PH_CHERWELL_SR_ID <= @cherwell_sr_to
AND IDE.ID IN (NULL)
AND IDE.ID = @id
AND IDE.ID >= @id_from
AND IDE.ID <= @id_to
AND UPPER(N.DIARY_ENTRY) LIKE UPPER(@diaryentry_wildcard) ESCAPE '\'
AND N.DIARY_ENTRY IN (NULL)
AND N.DIARY_ENTRY = @diaryentry
AND N.DIARY_ENTRY >= @diaryentry_from
AND N.DIARY_ENTRY <= @diaryentry_to
AND MAX(N.ENTRY_DATE) IN (NULL)
AND MAX(N.ENTRY_DATE) = @entrydate
AND MAX(N.ENTRY_DATE) >= @entrydate_from
AND MAX(N.ENTRY_DATE) <= @entrydate_to

My dilemma is that the @Filter@ is required and I do not (yet?) know enough on how to control what it adds.

Thank you.

I would like to add this:  After some additional research of the break out of the @Filter@.  I believe the natural syntax to be the cause of my issue.  This is because of the "AND" statements.  Can this be edited? to be "OR" statements and the input of appropriate parenthesis? 

Thank you.

Don't get distracted by the @FILTER@ code that gets added to the query when you "preview" it - this gets added to every query and there is nothing you can do to influence it (and its not "wrong" in any way either, just ignore it and concentrate on the body of your query).

Going back to your original post ; the "Exact" message is nothing to do with the error, it is some screen text telling you that the "param_sr" field on screen is going to be used in an exact match. When you get errors in the application you need to look at the app-ca.log file on the server for the error message where there will be much more detail.

(I've not attempted to debug your NSQL ; urmas's comment about just 'wrapping' it in NSQL is the right thing to do though)

So each of the select listed fields in the NSQL will generate four Filter options.

The easiest way to see this is create a new Soap project in SoapUi using your NSQL wsdl.

https://cppm.ondemand.ca.com/niku/wsdl/query/query_code?wsdl

You will see that all the filter options are optional and if submitted with blank values or removed they aren't used by the query.

V/r,

Gene

Hi,

I'm looking for the correct WSDL to execute my NSQL. Please some out point out the WSDL?

Thanks,

Thenna

Note : https://cppm.ondemand.ca.com/niku/wsdl/query/query_code?wsdl is not working.

Try:

https://cppm.ondemand.ca.com/niku/wsdl/query/debugGene?tenantId=clarity 

and you might have to provide login credentials for the production system.  Our test system doesn't need  credentials.

V/r,

Gene