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 PremiseClarity 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:
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]
Create a new Custom Object and make it API Enabled.
Add a new API enabled attribute that uses the lookup.
Create some instances of this new object, set a value for the attribute.
In the Classic UX: order by the attribute. It correctly does ascending/descending sort.
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.