MUX - Dynamic lookup getting ORA-00907 missing right parenthesis error

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Dynamic lookup values displayed in classic- not in modern

MUX - Dynamic lookup getting ORA-00907 missing right parenthesis error

Steps to Reproduce: 
1. Create dynamic lookup
SELECT
@SELECT:departcode:ID@,
@SELECT:departcode||'-'||shortdesc:NAME@ 
FROM
(select departcode,shortdesc from 

(SELECT dept.departcode, dept.shortdesc, ocp.id, 'allocation' filtertype
    FROM DEPARTMENTS dept, PAC_MNT_PROJECTS pmp, odf_ca_project ocp
    where dept.departcode = pmp.departcode
    and pmp.id = ocp.id
union 
    SELECT dept.departcode, dept.shortdesc, ocp.id, 'portfolio' filtertype
    FROM
    DEPARTMENTS dept, PAC_MNT_PROJECTS pmp, odf_ca_project ocp
    where dept.departcode = pmp.departcode
    and pmp.id = ocp.id)
    where @FILTER@ AND (@WHERE:PARAM:USER_DEF:INTEGER:p_id@ = id OR
    @WHERE:PARAM:USER_DEF:INTEGER:p_id@ IS NULL))
2. Create subobject of the project object
3. Create lookup attribute in the subobject based on the lookup created in Step 1.
4. API enable the attribute.
5. Go to porjects in MUX and add a department
6. Go to subobject and create an instance.
7. Attempt to click on the custom lookup attribute to change the value and observe error in the app-ca.log 

Expected Results: It should not error

Actual Results: it's not showing any value and app-ca.log shows the error

Environment

Release : 16.0.0, 16.0.3

Resolution

DE67114, After review by Engineering team below proposed modification to the query is needed.

Engineering team analyzed the query and identified that there is an unnecessary additional select that wraps the actual query is causing the issue. Resolution is to remove the select (without having any functional difference) and the lookup starts working as expected. Please refer to the modified query below.

SELECT
@SELECT:departcode:ID@,
@SELECT:departcode||'-'||shortdesc:NAME@
FROM
(SELECT dept.departcode, dept.shortdesc, ocp.id, 'allocation' filtertype
FROM DEPARTMENTS dept, PAC_MNT_PROJECTS pmp, odf_ca_project ocp
where dept.departcode = pmp.departcode
and pmp.id = ocp.id
union
SELECT dept.departcode, dept.shortdesc, ocp.id, 'portfolio' filtertype
FROM
DEPARTMENTS dept, PAC_MNT_PROJECTS pmp, odf_ca_project ocp
where dept.departcode = pmp.departcode
and pmp.id = ocp.id)
where (@WHERE:PARAM:USER_DEF:INTEGER:p_id@ = id OR
@WHERE:PARAM:USER_DEF:INTEGER:p_id@ IS NULL) AND @FILTER@