Column ambiguously defined

Databases
1

Hi there,

I have stuck with my sql select which thrown the error of “column ambiguously defined”.
I am currently using oracle.

Here’s the code:


SELECT T_LC_TYPE.*, T_LC_CONDITION.DESCRIPTION_EN AS COND_DESCPT_EN, T_LC_CONDITION.DESCRIPTION_BM AS COND_DESCPT_BM, T_LC_DOC.DESCRIPTION_EN AS DOC_DESCPT_EN,
T_LC_DOC.DESCRIPTION_BM AS DOC_DESCPT_BM, T_REF_FREQUENCY.DESCRIPTION_EN AS FRE_DESCPT_EN, T_REF_FREQUENCY.DESCRIPTION_BM AS FRE_DESCPT_BM,
T_USER_DEPARTMENT.DESCRIPTION_EN AS DEPT_DESCPT_EN, T_USER_DEPARTMENT.DESCRIPTION_BM AS DEPT_DESCPT_BM FROM T_LC_TYPE, T_LC_CONDITION, T_LC_DOC, T_LC_COMMENTER, T_REF_FREQUENCY,
T_USER_DEPARTMENT
WHERE T_LC_TYPE.OBJECTID = '013C1DF933F56BA2'
AND T_LC_CONDITION.TYPE_OBJECTID = T_LC_TYPE.OBJECTID
AND T_LC_DOC.TYPE_OBJECTID = T_LC_TYPE.OBJECTID
AND T_LC_COMMENTER.USER_DEPARTMENT_OBJECTID = T_USER_DEPARTMENT.OBJECTID
AND T_LC_TYPE.FREQUENCY_OBJECTID = T_REF_FREQUENCY.OBJECTID
AND T_LC_TYPE.LICENSE_ISSUED_BY = T_USER_DEPARTMENT.OBJECTID
AND REQUEST_STATUS = 'APPROVED'

I stuck with this couple of hours and did not see the problem. Please advise.
Thanks in advance.

2

Easier to read in this form:


SELECT
T_LC_TYPE.*,
T_LC_CONDITION.DESCRIPTION_EN AS COND_DESCPT_EN,
T_LC_CONDITION.DESCRIPTION_BM AS COND_DESCPT_BM,
T_LC_DOC.DESCRIPTION_EN AS DOC_DESCPT_EN,
T_LC_DOC.DESCRIPTION_BM AS DOC_DESCPT_BM,
T_REF_FREQUENCY.DESCRIPTION_EN AS FRE_DESCPT_EN,
T_REF_FREQUENCY.DESCRIPTION_BM AS FRE_DESCPT_BM,
T_USER_DEPARTMENT.DESCRIPTION_EN AS DEPT_DESCPT_EN,
T_USER_DEPARTMENT.DESCRIPTION_BM AS DEPT_DESCPT_BM
FROM
T_LC_TYPE, T_LC_CONDITION, T_LC_DOC, T_LC_COMMENTER, T_REF_FREQUENCY, T_USER_DEPARTMENT
WHERE
T_LC_TYPE.OBJECTID = '013C1DF933F56BA2'
AND T_LC_CONDITION.TYPE_OBJECTID = T_LC_TYPE.OBJECTID
AND T_LC_DOC.TYPE_OBJECTID = T_LC_TYPE.OBJECTID
AND T_LC_COMMENTER.USER_DEPARTMENT_OBJECTID = T_USER_DEPARTMENT.OBJECTID
AND T_LC_TYPE.FREQUENCY_OBJECTID = T_REF_FREQUENCY.OBJECTID
AND T_LC_TYPE.LICENSE_ISSUED_BY = T_USER_DEPARTMENT.OBJECTID
AND REQUEST_STATUS = 'APPROVED'
3

AND T_LC_CONDITION.TYPE_OBJECTID = T_LC_TYPE.OBJECTID
AND T_LC_DOC.TYPE_OBJECTID = T_LC_TYPE.OBJECTID
AND T_LC_COMMENTER.USER_DEPARTMENT_OBJECTID = T_USER_DEPARTMENT.OBJECTID
AND T_LC_TYPE.FREQUENCY_OBJECTID = T_REF_FREQUENCY.OBJECTID
AND T_LC_TYPE.LICENSE_ISSUED_BY = T_USER_DEPARTMENT.OBJECTID

All that lot belongs in the FROM clause, which is where joins should be specified, taking one bit of that as an example

ON
FROM
T_LC_DOC
INNER JOIN
T_LC_TYPE
ON T_LC_DOC.TYPE_OBJECTID = T_LC_TYPE.OBJECTID

You’d add more tables to the query as needed.

Do you really need all the fields from the T_LC_TYPE table?

Field and table names should really be in lower-case as it makes a query easier to read

4

Didn’t the error mention the name of the ambiguous column?

Anyway, the only column name in your query without the specification of the table name is REQUEST_STATUS. Try adding the tablename to that.