ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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

book

Article ID: 213447

calendar_today

Updated On:

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.

Attachments