Dynamic Lookup query returns rows in Classic UI but not in MUX

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

A Dynamic Lookup was created and the following NSQL query was used

SELECT @SELECT:pou.unique_name:ID@,
@SELECT:nbo.level1_name:NAME@,
@SELECT:nbo.last_updated_date:last_updated_Date@ 
from nbi_dim_obs nbo
join prj_obs_units pou on pou.id = nbo.obs_unit_id
where nbo.obs_type_name='Division'
and pou.depth = 1
and @FILTER@

The lookup returns rows in the Classic UI but no rows are returned when it is used in the Modern UX.

Environment

Release : 16.1.0

Cause

The following error is seen in the app-ca.log when the query is executed from the MUX:

ERROR 2023-01-31 10:32:23,369 [http-nio-8080-exec-973] union.persistence (clarity:admin:71040644__4B9E1332-B485-4A95-A123-B129AAAD14FC:PPM_REST_API) Exception on nsql_internal statement null:5153783
org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
  Position: 396 

Resolution

Update the query as follows:

SELECT @SELECT:pou.unique_name:ID@,
@SELECT:nbo.level1_name:NAME@,
@SELECT:nbo.last_updated_date:last_updated_Date@ 
from nbi_dim_obs nbo
join prj_obs_units pou on pou.id = nbo.obs_unit_id
where 1=1 and @FILTER@ and nbo.obs_type_name='Division'
and pou.depth = 1

The lookup now returns rows in both the Classic and Modern UX.