Using a ROW_NUMBER Function in MSSQL query, sorting does not work in MUX

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

While using a lookup based on a query that has the ROW_NUMBER function in Microsoft SQL Server, ordering by the column in the Modern User Experience (UX does not work and makes all the results disappear.

Steps to Reproduce:

1. Create a new lookup:
   SELECT 
   @SELECT:NUM:[email protected],
   @SELECT:NUM:[email protected]
   FROM 
   (
   SELECT CAST (ROW_NUMBER() OVER(ORDER BY OBJECT_ID DESC) AS INT) NUM FROM SYS.ALL_OBJECTS
   ) WRAP
   WHERE @[email protected]
2. Create a new Custom Object and make it API Enabled.
3. Add a new API enabled attribute that uses the lookup.
4. Create some instances of this new object, set a value for the attribute.
5. In the Classic UX: order by the attribute. It correctly does ascending/descending sort.
6. In the Modern UX: order by the attribute.

Expected Results: It correctly does ascending/descending sort.
Actual Results: All rows for this custom object disappear. A "No Rows To Show" message is displayed.

Cause

This issue has been reported to Engineering as DE60703.

Environment

Release : 15.9.1 and 15.9.2 using Microsoft SQL Server

Component : CLARITY STUDIO

Resolution

This has been fixed in Clarity 15.9.3, as well as included in 15.9.2 patch #1.