When sorting in some portlets, data in the column being sorted on displays duplicate data

Document ID : KB000006425
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

When sorting in some portlets, such as the Investment Timesheet Summary  portlet, data in the column being sorted on displays duplicate data if the query used in the portlet contains a Group By clause in the underlying query. 

Steps to Reproduce:

1. Create a NSQL query with the following code:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:IDEA:i.id:IDEA_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.code:IDEA_CODE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.name:IDEA_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.stage_code:STAGE_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.stage_name:STAGE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.stage_name:STAGE_NAME@
FROM(
select i.id,
i.code, i.name, i.stage_code, l2.name stage_name
FROM inv_investments i
JOIN odf_ca_idea oci ON i.id = oci.id
LEFT JOIN cmn_lookups_v l2 ON l2.lookup_type = 'INV_STAGE_TYPE' AND l2.lookup_code = i.stage_code AND l2.language_code = 'en'
WHERE 1 = 1
GROUP BY i.id, i.code, i.name, i.stage_code, l2.name
) i
where @FILTER@

2. Then create a grid portlet with that query as the data provider
3. Add all fields to the layout
4. Add portlet to a page
5. Navigate to the portlet and click on any header other than idea_id to sort ascending (Ex idea_name)
6. Click on the same header again to sort descending

Expected Results: The results sorts by the value in descending order

Actual Results: Every row in the column ends up having the same value that you are sorting on

Environment:
This issue has been reproduced in 14.3 patch 3 and 4 but is not reproducible in 14.3 base or 14.4.
Cause:

This issue is caused by: CLRT-79985

Resolution:

Workaround:

Resort the data for the column that has the duplicating data in ascending order:

1. Navigate to the portlet with the issue

2. Click on the header row for the column that is duplicating data

This will resort the column in ascending order removing the duplicated data shown.

However, there is no workaround to use descending sort in NSQL portlets.

Resolution:

This issue is no longer reproducible in CA PPM 14.4 and higher.