SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Location
    Kuching, Sarawak
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    column ambiguously defined

    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:

    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. #2
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Location
    Kuching, Sarawak
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Easier to read in this form:

    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'

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •