SitePoint Sponsor

User Tag List

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

    Exclamation Column Ambiguously Defined

    Good day everyone,

    I am get the error of "ORA-00918: column ambiguously defined"

    When I run this query:-

    Code:
    SELECT
    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
    FROM
    T_LC_LICENSE_APPL LEFT JOIN T_LC_USER ON (T_LC_LICENSE_APPL.USER_OBJECTID = T_LC_USER.OBJECTID),
    T_LC_LICENSE_APPL_TYPE,
    T_LC_TYPE,
    T_REF_COUNTRY,
    T_REF_CITY,
    T_REF_STATE,
    T_USER_DEPARTMENT,
    T_LC_COMMENTER,
    T_LC_CATEGORY,
    T_LC_ENDORSE,
    T_LC_LICENSE_APPL_COMMENT,
    T_USER_DEPARTMENT
    WHERE
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Location
    Kuching, Sarawak
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I put this query in the Oracle SQL Developer and it produce this error when I run it.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    did it give any indication as to which column is the ambiguous one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2011
    Location
    Kuching, Sarawak
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.


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
  •