Dynamic Lookup Query Results Not Displaying In Modern (PostgreSQL)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

A dynamic lookup query does not works in the Classic, but not the Modern.
The lookup values displays in the Classic, but not the Modern.

The following sample query is used:

Select distinct
@select:p_year:fiscal_year@     
from
biz_com_periods
where
@filter@
order by p_year
limit 10

Lookup works in Classic:

However in the Modern, no values are returned:

Environment

Release : 16.0.3

Resolution

The results will only be displayed if the following are removed.

1. ORDER BY

As per documentation, the clause ORDER BY cannot be used in a dynamic query:

Use ORDER BY to specify requirements for sorting by one or more attributes. However, do not use an ORDER BY clause on a dynamic query that is used in a lookup. The Browse Window tab for the dynamic query already includes a Default Sorting Column field.

2. LIMIT