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
Release : 16.0.0, 16.0.3
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@