Getting error “msg 8156” on doing a merge with a left join

I am doing a merge with a number of left joins and they are using the same key I am using with my on statement, and I am getting from SQL Server:

Msg 8156, Level 16, State 1, Procedure sp_CommonLoanData, Line 41
The column ‘LNKEY’ was specified multiple times for ‘l’.
my the way the I have tested the select statement and it works and retrieves all of the data.
Code

  • Is it possible to use Microsoft 2013 sharepoint search server as my search engine for my site
  • ADO.NET Connection Pooling & SQLServer
  • Invalid column name for non-existent column or property
  • How to change previous and next 7 days using bar charts mvc c#?
  • cannot apply indexing with to an expression of type 'system.data.datatable'
  • Using Identity Insert on a linked server
  •    /*********************************************************************************
    Author: Herb Williams 
    Date: Oct 08 2014 
    Purpose: This stored procedure builds the CommonLoanData table in CTS from the replicated 
             Empower tables in CTS. It check the  Using Merge below is the process. 
             1. Update if there is a loan number
    
             2. Inserted if there is not a loan number 
    
    *********************************************************************************/
    
    CREATE PROCEDURE [dbo].[sp_CommonLoanData]
    
    ---------------------------------------
    -- I N S E R T  
    ---------------------------------------
    
    AS
    MERGE CommonLoanData AS cl
    USING (Select   l.LNKEY as mainKey, 
                    l.STOREDON,  
                    f.NEWFHACASEDATE,
                    m.ADJUSTED_BASE_RATE,
                    d.DISBDATE, 
                    w.COLLATERALORIGRECVD,
                    w.HEDGEDATE,
                    l.LNKEY,
                    b.BORR_LAST, 
                    b.BORR_FIRST,
                    p.PROPWILLBE,
                    lp.LPLANDESC,
                    pf.REF_PURP,
                    m.AMORTTYPE,
                    lp.NUMPMTS,
                    m.INTRATE,
                    m.LOANAMT,
                    m.LTV,
                    r.HOUSING_RATIO,
                    dd.EW_RETAXES,
                    dd.EW_HAZINS,
                    dd.EW_FLOODINS,
                    a.AUSRESULT,
                    t.REPCREDSCORE,
                    s3.HIGH_PRICED_LOAN,
                    m.PRC_BASE_PTS,`enter 
                    ma.POINTS_ADJ,
                    m.ADJUSTED_BASE_RATE,
                    w.ALL_IN_PRICE,
                    pa.ADJ_DESC,
                    pa.POINTS_ADJ,
                    m.LOCKINDATE,
                    m.LOCKINEXPIRES,
                    c.U_AGENT_WAREHOUSE,
                    i.AGENT_COMPANY,
                    repairs.amt,
                    s3.HIGH_PRICED_LOAN,
                    r.DEBT_RATIO,
                    m.CLTV,
                    l.STOREDON
    
    
                    from [EMPOWER].[LN_DBLOCKS] AS l
        left join [EMPOWER].[LN_MTGTERMS]       AS m  on  (m.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_MANUAL_PRC_ADJ] AS ma on  (ma.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_CODES]          AS cd on  (cd.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_DISBURSEMENTS]  AS d  on  (d.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_DEEDDATA]       AS dd on  (dd.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_BORRINFO]       AS b  on  (b.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_PLANDAT]        AS lp on  (lp.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_FHAVACLC]       AS f  on  (f.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_RATIOS]         AS r  on  (r.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_PROPINFO]       AS p  on  (p.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_PRICADJ]        AS pa on  (pa.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_PROPREFI]       AS pf on  (pf.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_SECTION35]      AS s3 on  (s3.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_AUS_RESULTS]    AS a  on  (a.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_CONTACTS]       AS c  on  (c.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_INVESTORS]      AS i  on  (i.LNKEY = l.LNKEY)
        left join [EMPOWER].[LN_TRANSUMM]       AS t  on  (t.LNKEY = l.LNKEY)
        left join [EMPOWER].[U_LN_WEB_BUILDER]  AS w  on  (w.LNKEY = l.LNKEY)
        LEFT JOIN (SELECT LNKEY, sum(ITEMCOST) amt FROM EMPOWER.LN_REPAIRFUNDS GROUP BY LNKEY) repairs on repairs.LNKEY = m.LNKEY)
    
            AS l
            ON (CommonLoanDataId = mainKey )
            and (l.STOREDON >  cl.LastUpdated)
    ---------------------------------------
    -- M E R G E D
    ---------------------------------------
    
    WHEN MATCHED
        THEN UPDATE 
        SET  cl.BranchMarginAdj               = m.ADJUSTED_BASE_RATE,
             cl.ApplicationDate               = m.APP_DATE,
             cl.FHACaseFileDate               = f.NEWFHACASEDATE,
             cl.FundedDate                    = d.DISBDATE,
             cl.CollateralReceivedDate        = w.COLLATERALORIGRECVD,
             cl.HedgedDate                    = w.HEDGEDATE,
             cl.LoanNumber              = l.LNKEY,
             cl.BorrowerLastName        = b.BORR_LAST, 
             cl.BorrowerFirstName       = b.BORR_FIRST,
             cl.Occupancy               = p.PROPWILLBE,
             cl.PropertyState           = p.PROP_STATE,
             cl.PropertyType            = null,
             cl.Program                 = lp.LPLANDESC,
             cl.DocType                 = null,
             cl.LoanType                = null,
             cl.LoanPurpose             = null,
             cl.RefiPurpose             = pf.REF_PURP,
             cl.LienPosition            = null,
             cl.FinanceMethod           = m.AMORTTYPE,
             cl.Term                    = lp.NUMPMTS,
             cl.NoteRate                = m.INTRATE,
             cl.LoanAmount              = m.LOANAMT,
             cl.Ltv                     = m.LTV,
             cl.Hsg                     = r.HOUSING_RATIO,
             cl.WaivedImpounds          = case when dd.EW_RETAXES = 1 or dd.EW_HAZINS = 1 or dd.EW_FLOODINS = 1 then 1 end,
             cl.AUSType                 = null,
             cl.AUSDecision             = a.AUSRESULT,
             cl.LoanFico                = t.REPCREDSCORE,
             cl.MIType                  = null,
             cl.HPMLFlag                = s3.HIGH_PRICED_LOAN,
             cl.SecondaryNotesCPExceptionNotes =null,
             cl.BondStylePrice          = m.PRC_BASE_PTS,
             cl.ProgramPriceAdjTotal    = ma.POINTS_ADJ,
             cl.AdjustedBasePrice       = m.ADJUSTED_BASE_RATE,
             cl.AllInPrice              = w.ALL_IN_PRICE,
             cl.LLPANameItemized        = pa.ADJ_DESC,
             cl.LLPAAmountItemized      = pa.POINTS_ADJ,
             cl.LockDate                = m.LOCKINDATE,
             cl.LockExpiration          = m.LOCKINEXPIRES,
             cl.LockCanceled            = null,
             cl.CondoType               = null,
             cl.AUSRiskDecision         = null,
             cl.WarehouseLine           = c.U_AGENT_WAREHOUSE,
             cl.Investor                = i.AGENT_COMPANY,
             cl.ExclusionIndicator      = case when repairs.amt > 0 or mcc > 0 or s3.HIGH_PRICED_LOAN > 0 then 1 else 0 end,
             cl.Dti                     = r.DEBT_RATIO,
             cl.Cltv                    = m.CLTV,
             cl.[Source]                = null,
             cl.LastUpdated             = l.STOREDON,
             cl.WireSentDate            = null, 
             cl.WireReturningDate       = null,
             cl.ShippedToInvestorDate   = null,
             cl.InvestorPriceLockDate   = null,
             cl.InvestorPriceLockExpiration = null,
             cl.FirstInvestorPaymentDate = null,
             cl.PAReceivedDate           = null
    
    
    WHEN NOT MATCHED
        THEN INSERT VALUES (
             m.ADJUSTED_BASE_RATE,
             m.APP_DATE,
             f.NEWFHACASEDATE,
             d.DISBDATE,
             w.COLLATERALORIGRECVD,
             w.HEDGEDATE,
             l.LNKEY,
             b.BORR_LAST, 
             b.BORR_FIRST,
             p.PROPWILLBE,
             p.PROP_STATE,
             null,
             lp.LPLANDESC,
             null,
             null,
             null,
             pf.REF_PURP,
             null,
             m.AMORTTYPE,
             lp.NUMPMTS,
             m.INTRATE,
             m.LOANAMT,
             m.LTV,
             r.HOUSING_RATIO,
             case when dd.EW_RETAXES = 1 or dd.EW_HAZINS = 1 or dd.EW_FLOODINS = 1 then 1 end,
             null,
             a.AUSRESULT,
             t.REPCREDSCORE,
             null,
             s3.HIGH_PRICED_LOAN,
             null,
             m.PRC_BASE_PTS,
             ma.POINTS_ADJ,
             m.ADJUSTED_BASE_RATE,
             w.ALL_IN_PRICE,
             pa.ADJ_DESC,
             pa.POINTS_ADJ,
             m.LOCKINDATE,
             m.LOCKINEXPIRES,
             null,
             null,
             null,
             c.U_AGENT_WAREHOUSE,
             i.AGENT_COMPANY,
             case when repairs.amt > 0 or mcc > 0 or s3.HIGH_PRICED_LOAN > 0 then 1 else 0 end,
             r.DEBT_RATIO,
             m.CLTV,
             null,
             l.STOREDON,
             null, 
             null,
             null,
             null,
             null,
             null,
             null);
    
    SELECT  resource_type, request_mode, resource_description
    FROM    sys.dm_tran_locks
    
    SELECT * FROM cl
    ROLLBACK 
    

    One Solution collect form web for “Getting error “msg 8156” on doing a merge with a left join”

    You need to specify the columns of the derived table linstead of using *. it currently has about 15 columns called LNKEY.

    With the updated SQL you now have two definitions of StoredOn

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.