• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1511
  • Last Modified:

SQL "multi-part identifier cannot be bound" Error

I have to create a View that is extremely similar to another view expect for changes in Field Identifier values. I used the first as a template for the one I am trying to create. As per usual, I have looked at this thing a 1000 times and know there must be a disconnect or typo somewhere that I am not seeing.

I get the following two errors.

Msg 4104, Level 16, State 1, Procedure vPVTDetail, Line 2
The multi-part identifier "A379.EntityID" could not be bound.
Msg 4104, Level 16, State 1, Procedure vPVTDetail, Line 2
The multi-part identifier "A46.EntityID" could not be bound.

CREATE VIEW [dbo].[vPVTDetail] AS  
SELECT A1.EntityID,[PVT Loan Name],ParentEntityID,[Base],[Loan Date],[Finance Terms],[Principal],[DSRF Rate],[Amortization Term],[DSCR],[Year Sized],[Int. Only Period Info],[Int. Only Period - Yrs],[Benchmark Index (Period)],[Credit Spread],[Underlying Rate Per],[As of Date Per],[Index Information],[Benchmark Index],[Underlying Rate In],[As of Date Ind],[BPS Spread / Index],[Credit Spread - bps],[Lockbox/Servicing Fee],[Credit Enhancement/Insu],[Forward Premium - bps],[Other - bps],0 AS [Tot. Interest Rate],[Fees, Costs, Exp. & Res],[Underwriting Fee%],0 AS [Underwriting Fee Amt],[Debt. Serv. Reserve - mo],[Debt Serv. Reserve $],[Surety Policy Cost $],[Max Annual Debt Serv.],[Annual Reg. Costs],[Annual Trustee Fees],[Annual Surveillance Fees],[Legal Expenses $],[Rating Agency $],[Transfer Taxes $],[Title Insurance $],[Surveys $],[Bond Fee $],[Commitment Fee $],[Other Third Party $],[Environmental Fee $],[Appraisals $],[Other Expenses, Costs and Fees],0 AS [Total Exp. $],0 AS [Total Exp. % of Princ],0 AS [Principal Minus Expenses],[All-In Finance Percent],[Interest/Loan Life],[All-In Finance Cost],[Spec. Prov. (PEP 74,83)],[Min DCR(IDP)],[Min DCR(Post IDP)]
FROM ( SELECT EntityID,EntityName AS [PVT Loan Name] 
FROM dbo._EntityName WHERE EntityType = 105 AND IsDeleted = 0) A1 
LEFT JOIN ( SELECT EntityID,RowNumber,CAST(DetailValue AS int) AS ParentEntityID,EntityName AS [Base] 
FROM dbo._DetailsEntity WHERE FieldID = 9320) A2 ON A1.EntityID = A2.EntityID 
LEFT JOIN ( SELECT EntityID,RowNumber,CAST(DetailValue AS datetime) AS [Loan Date] 
FROM dbo._DetailsVarchar WHERE FieldID = 9438) A3 ON A1.EntityID = A3.EntityID 
LEFT JOIN ( SELECT EntityID,RowNumber,CAST(DetailValue AS money) AS [Finance Terms] 
FROM dbo._DetailsVarchar WHERE FieldID = 9336) A4 ON A1.EntityID = A4.EntityID 
LEFT JOIN ( SELECT EntityID,RowNumber,CAST(DetailValue AS decimal(19,4)) AS [Principal]

Open in new window

0
s_khan
Asked:
s_khan
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your code seems cut off, as the last LEFT JOIN does not have any ON <condition> part?!

anyhow, please avoid doing those subselects... unless you really use CTE expressions.

see here how you can make that much easier to read and usually also more performant:
CREATE VIEW [dbo].[vPVTDetail] AS  
SELECT A1.EntityID, A1.EntityName [PVT Loan Name]
, CAST(A2.DetailValue AS int) ParentEntityID
, A2.EntityName [Base]
, CAST(A3.DetailValue AS datetime) [Loan Date]
... etc .
FROM ( SELECT EntityID, AS [PVT Loan Name] 
FROM dbo._EntityName A1
LEFT JOIN dbo._DetailsEntity A2
  ON A1.EntityID = A2.EntityID 
 AND A2.FieldID = 9320
LEFT JOIN dbo._DetailsVarchar A3
  ON A1.EntityID = A3.EntityID 
 AND A3.FieldID = 9438
... etc ...
 
WHERE A1.EntityType = 105 
  AND A1.IsDeleted = 0
 

Open in new window

0
 
s_khanAuthor Commented:
Thank you so much for your speedy reply. Although your suggestion to rewrite would probably be a fantastic idea, I was able to find the boo-boos that produced the errors. The error text did not reference where the typos were, making things difficult.

"A379.EntityID" turned out to be "A37.EntityID"
AND
I had two instances of "A46.EntityID"
0
 
s_khanAuthor Commented:
Thank you for your help. Your suggestion raised some good questions for me and will help in my learning.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now