I have a problem with a custom entity referencing a Person entity. The way the SQL is generated make an ambiguous SQL query (ORA-00918). Here is the generated query:

SELECT * FROM (SELECT RECORDSET_NAME.*, ROWNUM RNUM FROM ( SELECT roles_RolesID, ROLES_NAME, ROLES_OPPORTUNITYID, ROLES_PERSONID, ROLES_LEADID, ROLES_CASEID, ROLES_OPPCASELEAD_COMP, ROLES_OPPCASELEAD_ID,ROLES_COMPANYID,roles_PersonId FROM Roles WHERE ((UPPER(ROLES_NAME) LIKE N'%' OR NVL(ROLES_NAME, N' ') = N' ') OR (UPPER(ROLES_OPPORTUNITYID) LIKE '%' OR NVL(ROLES_OPPORTUNITYID, 0) = 0) OR (UPPER(ROLES_PERSONID) LIKE '%' OR NVL(ROLES_PERSONID, 0) = 0) OR (UPPER(ROLES_LEADID) LIKE '%' OR NVL(ROLES_LEADID, 0) = 0) OR (UPPER(ROLES_CASEID) LIKE '%' OR NVL(ROLES_CASEID, 0) = 0) OR (UPPER(ROLES_OPPCASELEAD_COMP) LIKE N'%' OR NVL(ROLES_OPPCASELEAD_COMP, N' ') = N' ') OR (UPPER(ROLES_OPPCASELEAD_ID) LIKE N'%' OR NVL(ROLES_OPPCASELEAD_ID, N' ') = N' ')) and roles_Deleted is Null order by roles_Name ) RECORDSET_NAME WHERE ROWNUM

With sage 6.2 it was generating the following request which is correct:

SELECT roles_RolesID, ROLES_NAME,ROLES_OPPORTUNITYID,ROLES_PERSONID,ROLES_LEADID,ROLES_CASEID,ROLES_OPPCASELEAD_COMP,ROLES_OPPCASELEAD_ID,ROLES_COMPANYID,roles_PersonId FROM Roles WHERE ((UPPER(ROLES_NAME) LIKE N'%' OR NVL(ROLES_NAME, N' ') = N' ') OR (UPPER(ROLES_OPPORTUNITYID) LIKE '%' OR NVL(ROLES_OPPORTUNITYID, 0) = 0) OR (UPPER(ROLES_PERSONID) LIKE '%' OR NVL(ROLES_PERSONID, 0) = 0) OR (UPPER(ROLES_LEADID) LIKE '%' OR NVL(ROLES_LEADID, 0) = 0) OR (UPPER(ROLES_CASEID) LIKE '%' OR NVL(ROLES_CASEID, 0) = 0) OR (UPPER(ROLES_OPPCASELEAD_COMP) LIKE N'%' OR NVL(ROLES_OPPCASELEAD_COMP, N' ') = N' ') OR (UPPER(ROLES_OPPCASELEAD_ID) LIKE N'%' OR NVL(ROLES_OPPCASELEAD_ID, N' ') = N' ')) and NVL(ROLES_COMPANYID, 0) = 3909 and roles_Deleted is Null order by roles_Name

The fact that we have two times the field ROLES_PERSONID is a problem for Oracle in the first query and lead to the error. Is there a way to correct this problem or a patch?

Best regards