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

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:hidden_key@,
@SELECT:Z.hidden_code:hidden_code@,
@SELECT:Z.hidden_name: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 @FILTER@
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.

Environment

Release : 15.9.3

Component : Clarity MUX UI Usability

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

Resolution

DE62809, As per Engineering review it is not a product defect. Below is the desposition and documentation has been updated.

Adding 'ORDER BY' in the NSQL of dynamic lookup is redundant and it doesn't serve any purpose since Classic PPM has 'Default sorting column' and 'Default sort order' configurations in the lookup definition. Clarity adds the default ORDER BY clause dynamically based on these configurations and overwrites the 'order by' clause written in NSQL. Hence, Broadcom recommends that you avoid using the 'ORDER BY' clause in the NSQL while defining dynamic lookups.

Link to documentation here

Workaround: Remove ORDER BY clause from NSQL