Clarity

  • 1.  NSQL Lookup with Parameter

    Posted Jan 18, 2017 11:56 PM

    Hi All,

    I'm stuck on this one.  I'm trying to make a NSQL lookup on Risk Object to give the option of another SubObject of Project.

    For example, I have under a Project X, Releases 1, 2, 3, 4, 5

    On the risk, I only want to see results for the lookup in Risk on Project X, releases 1, 2, 3, 4, 5

     

    Object:

    Project

     

    Subobject:

    Risk

    Release

     

    Lookup:

    on Risk, needs to pull Release Items connected with the Project.

     

    Question 1:  How do I return the ID of the Parent object?

     

    Question 2: What is wrong with this query where it's returning no data?

    SELECT  DISTINCT
    @SELECT:RI.PK_ID:ID@,
    @SELECT:REL.TRG_REL_NAME:REL_NAME@,
    @SELECT:REL.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,
    @SELECT:'en':LANGUAGE_CODE@

    FROM    odf_ca_trg_release_obj REL
    INNER JOIN RIM_RISKS_AND_ISSUES RI ON REL.ODF_PARENT_ID = RI.PK_ID

    WHERE  
    (@WHERE:PARAM:USER_DEF:STRING:PROJ_ID@ = RI.PK_ID)

    AND REL.TRG_REL_STAT = 'trg_active'
    AND     @FILTER@



  • 2.  Re: NSQL Lookup with Parameter

    Posted Jan 19, 2017 12:07 AM

    Check this:

     

    NSQL INPUT PARAMTER

    https://communities.ca.com/message/241713014 

     

     NJ



  • 3.  Re: NSQL Lookup with Parameter

    Posted Jan 19, 2017 12:32 AM

    Hi navzjoshi00,

    I appear to be using that.  My link, I'm selecting "pk_id" from the Risk Subobject.

     

    I did make a correction to my NSQL Lookup above:  I changed "STRING" to "INTEGER", because it is using the ID.

     

    I run the query in Toad and get a result.  Of course, I select the "PK_ID" from the Risk object and I get my list of results.

     

    When I run this NSQL in CA PPM, I get no results.

     

    Is there a way to find out if the "PARAM" is actually gathering data?  How do I review this?



  • 4.  Re: NSQL Lookup with Parameter

    Posted Jan 19, 2017 04:47 AM

    It all sounds like you are doing the right thing to me ; I'd double check that you have the mapping in the attribute set correctly though, can't think of anything else that would give the same symptoms.

     

    I would map to [--Object ID--] in the attribute mapping (rather than pk_id like you say you have - but I'm not really sure what the difference is) - you could try that? 



  • 5.  Re: NSQL Lookup with Parameter

    Posted Jan 19, 2017 07:31 PM

    Hi David,

    How do I find out what PK_ID or [--Object ID--] is pulling?  When I run the query using Toad and the ID of the current RISK, it returns my results.  But, when I put this in Clarity, I get no results.



  • 6.  Re: NSQL Lookup with Parameter

    Posted Jan 20, 2017 03:33 AM

    Not sure its worth debugging, have you just trird the [--Object Id--] ?



  • 7.  Re: NSQL Lookup with Parameter
    Best Answer

    Posted Jan 24, 2017 01:47 PM

    Hi All,

    Thanks for your help on this!  I finally got it working with the following:

    SELECT  DISTINCT
    @SELECT:REL.ID:ID@,
    @SELECT:REL.TRG_REL_NAME:REL_NAME@,
    @SELECT:REL.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,
    @SELECT:'en':LANGUAGE_CODE@
    FROM    odf_ca_trg_release_obj REL
    INNER JOIN INV_INVESTMENTS I on I.ID = REL.ODF_PARENT_ID
    INNER JOIN RIM_RISKS_AND_ISSUES RI ON I.ID = RI.PK_ID

    WHERE
    RI.ID = @WHERE:PARAM:USER_DEF:INTEGER:PROJ_ID@
    AND REL.TRG_REL_STAT = 'trg_active'

    AND @FILTER@

     

    The Param I used is the [--Object ID--].

     

    It turned out that my joins for the tables weren't correct.