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
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
I think you're describing this situation?
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
select id, null, link2
You would need to change this to a query like:
select id, max(link1) as link1, max(link2) as link2
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):
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.
^ 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.