locked
T-SQL ambiguous column, invalid column in inner join RRS feed

  • Question

  • User1045460610 posted

    When I join to the table for APPID I get a ambiguous column error. If I put the prefix for the alias I get invalid column. What's the correct syntax for a join with the same column in multiple tables?

    SELECT TOP (1000) [TH.APPID] --APPID ambiguous column, invalid column TH.APPID
    ,NM.LAST_NAME
    ,NM.FIRST_NAME
    ,[SOURCE_CDE]
    ,[GROUP_NUM]
    ,[TRANS_KEY_LINE_NUM]
    ,[TRANS_DTE]
    ,[TRANS_AMT]
    ,[TRANS_DESC]
    ,[FOLIO]
    ,[ACCT_CDE]
    ,[PROJECT_CODE] --ambiguous column
    ,[ENCUMB_GL_FLAG]
    ,[ENCUMB_GL_TRANS_ST]
    ,[AP_SBS_ID_NUM]
    ,[AP_SBS_CDE_SUBSID]
    ,[INVOICE_NUM]
    ,[ID_NUM] --ambiguous column
    ,[SUBSID_CDE]
    ,[OFFSET_FLAG]
    ,[SUBSID_TRANS_STS]
    ,[PAYABLE_CHECK_DTE]
    ,[CHECK_NUM_ALPHA]
    ,[CHECK_NUM_NUM]
    ,[TRANS_PO_NUM_GRP_N]
    ,[PO_LINE_NUM]
    ,[DISCOUNT]
    ,[RECEIPT_NUM]
    ,[ABA_NUM]
    ,[AR_CDE]
    FROM [TmsePrd].[dbo].[TRANS_HIST] TH
    INNER JOIN DIVISION_DEF DD ON TH.APPID = DD.APPID
    INNER JOIN SECTION_MASTER SM ON TH.APPID = SM.APPID
    INNER JOIN NAME_MASTER NM ON TH.APPID = NM.APPID AND TH.ID_NUM = NM.ID_NUM
    WHERE DIV_CDE = 'DC'
    AND DIVISION_CDE = 'UG'
    OR DIVISION_CDE = 'GR'
    AND TH.TRANS_DESC = 'SubTerm A Technology Fee'
    OR TH.TRANS_DESC = 'SubTerm B Technology Fee'

    Tuesday, July 9, 2019 4:44 PM

Answers

  • User77042963 posted

     TH.[APPID] 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 9, 2019 5:25 PM
  • User753101303 posted

    Hi,

    Each component is escaped separetely ie this is [TH].[APPID].

    [TH.APPID] would be column whose name is really "TH.APPID"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 9, 2019 5:28 PM

All replies

  • User77042963 posted

     TH.[APPID] 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 9, 2019 5:25 PM
  • User753101303 posted

    Hi,

    Each component is escaped separetely ie this is [TH].[APPID].

    [TH.APPID] would be column whose name is really "TH.APPID"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 9, 2019 5:28 PM