Good day everyone,
I am get the error of "ORA-00918: column ambiguously defined"
When I run this query:-
T_LC_LICENSE_APPL.APPLICATION_NO AS APPLICATION_NO,
T_LC_LICENSE_APPL.REMARKS AS REMARKS,
T_LC_LICENSE_APPL.USER_OBJECTID AS USER_OBJECTID,
T_LC_LICENSE_APPL_TYPE.OBJECTID AS APPL_TYPE_OBJID,
CASE WHEN T_LC_LICENSE_APPL.NAME = '' OR T_LC_LICENSE_APPL.NAME IS NULL THEN T_LC_USER.NAME ELSE T_LC_LICENSE_APPL.NAME END AS APPLICANT_NAME,
NVL(T_LC_LICENSE_APPL.COR_ADDRESS1,'') AS GUEST_ADDR1,
NVL(T_LC_LICENSE_APPL.COR_ADDRESS2,'') AS GUEST_ADDR2,
NVL(T_LC_LICENSE_APPL.COR_ADDRESS3,'') AS GUEST_ADDR3,
T_LC_LICENSE_APPL.COR_POSTCODE AS GUEST_POSTCODE,
NVL(T_LC_USER.COR_ADDRESS1,'') AS REG_USER_ADDR1,
NVL(T_LC_USER.COR_ADDRESS2,'') AS REG_USER_ADDR2,
NVL(T_LC_USER.COR_ADDRESS3,'') AS REG_USER_ADDR3,
T_LC_USER.COR_POSTCODE AS REG_USER_POSTCODE,
T_LC_TYPE.LICENSE_TYPE_BM AS LIC_TYPE,
T_LC_LICENSE_APPL.CREATED_DATE AS APPLICATION_DATE,
T_REF_COUNTRY.DESCRIPTION_BM AS GUEST_COUNTRY,
T_REF_CITY.DESCRIPTION_BM AS GUEST_CITY,
T_REF_STATE.DESCRIPTION_BM AS GUEST_STATE,
T_REF_COUNTRY.DESCRIPTION_BM AS REG_USER_COUNTRY,
T_REF_CITY.DESCRIPTION_BM AS REG_USER_CITY,
T_REF_STATE.DESCRIPTION_BM AS REG_USER_STATE,
T_USER_DEPARTMENT.CODE AS CATE_DEPART,
T_LC_ENDORSE.CODE AS COMM_STAT,
T_LC_LICENSE_APPL_COMMENT.REMARK AS COMM_REMARK
T_LC_LICENSE_APPL LEFT JOIN T_LC_USER ON (T_LC_LICENSE_APPL.USER_OBJECTID = T_LC_USER.OBJECTID),
T_LC_LICENSE_APPL.OBJECTID = T_LC_LICENSE_APPL_TYPE.LICENSE_APPL_OBJECTID
AND T_LC_COMMENTER.USER_DEPARTMENT_OBJECTID = T_USER_DEPARTMENT.OBJECTID
AND T_LC_CATEGORY.OBJECTID = 'A1'
AND T_LC_LICENSE_APPL_COMMENT.LICENSE_APPL_TYPE_OBJECTID = T_LC_LICENSE_APPL_TYPE.OBJECTID
AND T_LC_LICENSE_APPL_COMMENT.DEPT_ID = T_USER_DEPARTMENT.OBJECTID
AND T_LC_ENDORSE.OBJECTID = T_LC_LICENSE_APPL_COMMENT.STATUS
AND T_REF_COUNTRY.OBJECTID = T_LC_LICENSE_APPL.COR_COUNTRY_OBJECTID
AND T_REF_CITY.OBJECTID = T_LC_LICENSE_APPL.COR_CITY_OBJECTID
AND T_REF_STATE.OBJECTID = T_LC_LICENSE_APPL.COR_STATE_OBJECTID
AND T_REF_COUNTRY.OBJECTID = T_LC_USER.COR_COUNTRY_OBJECTID
AND T_REF_CITY.OBJECTID = T_LC_USER.COR_CITY_OBJECTID
AND T_REF_STATE.OBJECTID = T_LC_USER.COR_STATE_OBJECTID
AND T_LC_TYPE.OBJECTID = T_LC_LICENSE_APPL_TYPE.TYPE_OBJECTID
AND T_LC_LICENSE_APPL.OBJECTID IN ('013EC99C032B6A1B','013EC152365726A6')
AND T_USER_DEPARTMENT.CODE IN ('JTK','PB', 'JB', 'PL')
ORDER BY T_LC_LICENSE_APPL.APPLICATION_NO, T_LC_TYPE.CODE
Please advise. Thanks
that error message is produced when a column name exists in more than one table and is not properly qualified by its table name
all of your coluumns appear to be properly qualified by their table names
are you sure this is the query that produced the error message?
Yes, I put this query in the Oracle SQL Developer and it produce this error when I run it.
did it give any indication as to which column is the ambiguous one?
It was my careless mistake, one of the column name is wrongly spelled.
Thanks for explaining.
This topic is now closed. New replies are no longer allowed.