Clarity PPM

Expand all | Collapse all

Trying to combine two object links (old vs new) into one column of NSQL query/portlet using UNION

  • 1.  Trying to combine two object links (old vs new) into one column of NSQL query/portlet using UNION

    Posted 03-31-2016 02:18 PM

    We are running CA PPM 14.3.  I am creating a NSQL query/portlet and have the following question.  The query is a UNION query between an old sub-object of the project object (custom Risk Assessment object) and a newer version of this sub-object.  The query returns rows from the old and new objects.  I have created two separate links to connect to the Properties page of the old sub-object and the Properties page of the new sub-object.  My links are working for both sub-objects .... but I have to return two columns in the query .... one column for the old link and one column for the new link.  One of these columns is always blank for each row.  My question is ..... is there a way to combine the columns so that I can place the correct (old or new) link into the one column?  Thanks for any help!  Greg P



  • 2.  Re: Trying to combine two object links (old vs new) into one column of NSQL query/portlet using UNION

    Posted 04-01-2016 04:02 AM

    If I understood correctly, this should help you http://www.nbl.fi/~nbl3674/Clarity/urlredirection.htm

    You can also search the community with Keywords like URL Redirection etc



  • 3.  Re: Trying to combine two object links (old vs new) into one column of NSQL query/portlet using UNION

    Posted 04-04-2016 04:28 PM

    I think you're describing this situation?

     

    Row numberIDLink1Link2
    1Item1http://oldlinktoitem1null
    2Item1nullhttp://newlinktoitem1
    3Item2http://oldlinktoitem2null
    4Item2nullhttp://newlinktoitem2
    XItemX...http://oldlinktoitemxnull

     

    If so, you'll need to do a couple of things for your query.

     

    The first will be to check what you are using internally in your query for the dimension key field - because you'll need to use the same value for both rows on each item.  E.g. If rows numbered 1 and 2 above are also having unique values in their @SELECT:DIM:....@ column values, the rows cannot be merged, and another value that is common to both rows but still offering uniqueness across the result set (e.g. the 'ID' column in my case would work for that) would need to be used instead.

     

    Next you'll need to GROUP your results which will probably also require putting your existing query into (another) subquery.

     

    For example, if the following contrived/theoretical query produces the results above:

     

    select id, link1, null as link2

    from sometableA

    union

    select id, null, link2

    from sometableB

     

    You would need to change this to a query like:

     

    select id, max(link1) as link1, max(link2) as link2

    from

    (

    select id, link1, null as link2

    from sometableA

    union

    select id, null, link2

    from sometableB

    ) subq

    group by id

     

    (Aggregation functions like 'max' on a string tend to return the non-null value, but another function or operation can be used instead if preferred or appropriate).

     

    Then the results in the query would collapse down to this (which I think you're aiming for based on your description):

     



  • 4.  Re: Trying to combine two object links (old vs new) into one column of NSQL query/portlet using UNION

    Posted 04-04-2016 04:31 PM

    In case you then really wanted to have the results also in a single column as well as a single row, within the aggregation methods or outside of that if contained in a subquery, you should be able to do it.  E.g. max(link1) || max(link2) or similar too.



  • 5.  Re: Trying to combine two object links (old vs new) into one column of NSQL query/portlet using UNION

    Posted 04-05-2016 03:33 AM

    ^ Don't think its event that 'complex' ; just a simple CASE statement in the NSQL could combine URLs into one column

     

         CASE WHEN something THEN url1 ELSE url2 END my_link

     

    Of course all this is still reliant upon the "URL-redirection" technique that sridhar is alluding to in the first response. Not sure I can visualise how to do it with out that technique though, just be using built-in Clarity "links" which I think was really the subtext of the original question.

     

    --

     

    TL;DR search for "URL redirection" on here.