In the New UX lookups show no values (no results found) when attempting to select it.

book

Article ID: 225577

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

SUMMARY: In the New UX lookup based attribute show no values (no results found) when attempting to select it when ORDER BY used in the NSQL query..

STEPS TO REPRODUCE: 

1. Create lookup with below NSQL
SELECT
@SELECT:Z.hidden_key:[email protected],
@SELECT:Z.hidden_code:[email protected],
@SELECT:Z.hidden_name:[email protected]
from (select a.id hidden_key, a.code hidden_code, a.name hidden_name from (select inv.id, inv.code, inv.name from inv_investments inv ) a) Z
WHERE 1=1 and @[email protected]
ORDER BY
Z.hidden_key
2. Create attribute on the project object based on above lookup.
3. Put the attribute in the MUX for project grid
4. Attempt to select a value, no results found

Expected Results: It should allow to select a value in MUX.

Actual Results: It is not allowing to select a value in MUX.

Cause

ERROR 2021-10-06 14:34:27,150 [http-nio-1593-exec-1916] osf.LookupSuggestService (clarity:admin:5318308__0EEBB282-75E6-497E-8B69-56ADECD378D7:PPM_REST_API) 
com.niku.union.persistence.nsql.NSQLException: com.niku.union.persistence.PersistenceException: 
SQL error code: 907
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00907: missing right parenthesis

Executed:
select * from (select row_number() over ( order by hidden_code  asc) row_num, count(*) over () num_rows , q.* from ( SELECT
Z.hidden_key AS hidden_key,
Z.hidden_code AS hidden_code,
Z.hidden_name AS hidden_name
from (select a.id hidden_key, a.code hidden_code, a.name hidden_name from (select inv.id, inv.code, inv.name from inv_investments inv ) a) Z
WHERE 1=1 and 1=? and 1=1 and 2 = 2  ORDER BY
Z.hidden_key  and (  NLS_UPPER( Z.hidden_code  ) like NLS_UPPER( ? ) ESCAPE '\'  ) ) q) q where q.row_num between ? and ? order by q.row_num

Environment

Release : 15.9.3

Component : Clarity MUX UI Usability

Resolution

DE62809, Under review by Engineering.

Workaround: Remove ORDER BY clause from NSQL