The multi part identifier could not be bound ошибка sql

Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.

Ответы

• Please re-read all my replies one more time carefully.

  Once again.

  1. Start with the query you currently have as subquery (the one you aliased as t). Make sure it works and returns Department, my_Month, someOtherColumn.

  2. Once you have that query working for you correctly (no typos), turn this query into CTE (common table expression for simplicity), e.g.

  ;with cte as (here goes the working query)

  select * from cte — one more check

  The ; before cte is important and should not be omitted.

  3. If you have select * from cte working, then proceed with your final query that should be

  select * from cte PIVOT (count(SomeOtherColumn) 

  for my_month in ([January], [February], etc.)) pvt

  ——————

  I suggest to get my_month using the name of the month, e.g. DATENAME function, this way you would not need changing [1], [2], etc. into English month names and can simply use select *

  —————

  That’s all what is needed here and if you can follow this simple plan, you’ll get your PIVOT working.

  ---

  • Изменено

    20 июля 2015 г. 15:32

  • Помечено в качестве ответа
    Dan Rooney
    21 июля 2015 г. 9:45

• Please re-read all my replies one more time carefully.

  Once again.

  1. Start with the query you currently have as subquery (the one you aliased as t). Make sure it works and returns Department, my_Month, someOtherColumn.

  2. Once you have that query working for you correctly (no typos), turn this query into CTE (common table expression for simplicity), e.g.

  ;with cte as (here goes the working query)

  select * from cte — one more check

  The ; before cte is important and should not be omitted.

  3. If you have select * from cte working, then proceed with your final query that should be

  select * from cte PIVOT (count(SomeOtherColumn) 

  for my_month in ([January], [February], etc.)) pvt

  ——————

  I suggest to get my_month using the name of the month, e.g. DATENAME function, this way you would not need changing [1], [2], etc. into English month names and can simply use select *

  —————

  That’s all what is needed here and if you can follow this simple plan, you’ll get your PIVOT working.

  ---

  Thank you, thank you, thank you Naomi!! This is pretty much my working query for those wanting to know…

  ;with cte as
  (
  SELECT datepart(mm, camgrp.datopn) AS my_month, v_Dept.depts AS [Department], another_column
  FROM v_Dept
   RIGHT OUTER JOIN camgrp ON v_Department.ds_reckey = camgrp.mg_depart
   LEFT OUTER JOIN  client ON camgrp.mg_cli = client.cl_code
  WHERE        (client.clgrp IN (‘YOUR’, ‘EWM’))
  )
  SELECT * FROM cte

  PIVOT (
  COUNT([another_column])
    FOR my_month IN([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12])
  ) p

  WHERE [Department] IS NOT NULL

  • Помечено в качестве ответа
    Eric__Zhang
    27 июля 2015 г. 9:49

Answers

• In your select statement there is no reference to this table (rentalCostTable), so that’s why you’re getting the error:

  SELECT locationTypesTable.locationTypes, rentalCostTable.rentalCost, PFR.shortDescription, PFR.propertyRef, propertyTypesTable.propertyType, PFR.numBeds
   FROM propertysForRentTable PFR
   INNER JOIN
    propertyTypesTable ON PFR.typeOfProperty = propertyTypesTable.keyID
   INNER JOIN
    locationTypesTable ON PFR.location = locationTypesTable.keyID 

  In order to resolve the error, you must include this table in the FROM clause, e.g. JOIN with it based on some field.

  SELECT locationTypesTable.locationTypes, rentalCostTable.rentalCost, PFR.shortDescription, PFR.propertyRef, propertyTypesTable.propertyType, PFR.numBeds
   FROM propertysForRentTable PFR
   INNER JOIN
    propertyTypesTable ON PFR.typeOfProperty = propertyTypesTable.keyID
   INNER JOIN
    locationTypesTable ON PFR.location = locationTypesTable.keyID 
  
  INNER JOIN rentalCostTable ON
  PFR.RentalID = rentalCostTable.RentalID -- use the actual field to join the tables

  ---

  Premature optimization is the root of all evil in programming. (c) by Donald Knuth

  Naomi Nosonovsky, Sr. Programmer-Analyst

  My blog

  • Marked as answer by

    Wednesday, September 15, 2010 11:25 PM

• This was a bug in the older versions of SQL Server. We fixed the problems with the ORDER BY clause in SQL Server 2005. This is documented under the following topic:

  You should also check out the other backward compatibility topics:

  Also, I should note that the query above will run fine if the database compatibility level is set to 80 or below. You can do this by using the sp_dbcmptlevel system SP but please read the corresponding topic in Books Online before going this route. The database compatibility level for databases upgraded from SQL Server will automatically be at 80 so your existing code should run fine.

• Greetings.  I ran into this because I had alias conflicts where the table name and the field name were the same.  Once I gave the table a true alias (e.g. «z3») with no conflict,
  it worked fine.

   UPDATE #LocTracker

  SET ErrorCode
  = 3
  —‘Unknown ZIP3’

  FROM #LocTracker lt

  LEFT
  OUTER
  JOIN US_TC_RMS_DB_MGMT..ZIP3
  ON zip3.ZIP3
  =
  LEFT(lt.POSTALCODE,3)

  WHERE lt.POSTALCODE
  is
  null

  AND ErrorCode
  = 0
  

   Gave me this error:

  The multi-part identifier «zip3.ZIP3» could not be bound.

  Certainly zip3.ZIP3 referencing is not Best Practices.

  UPDATE can be very tricky. Note above that #LocTracker is used after the UPDATE then after the FROM with alias. While valid T-SQL, I always found it confusing.
  

  Referring to the sample below, I just put the table name with alias after FROM, and
  after UPDATE I use the table alias. I also use table alias for each column.

  Same consideration if you are JOINing to subselect (derived table).

  The advantage of such an approach is that you take out the guesswork from the query.
  You tell precisely the database engine what should it do.
  

  
  USE tempdb;
  SELECT * INTO SOH
  FROM AdventureWorks2008.Sales.SalesOrderHeader
  SELECT * INTO SOD
  FROM AdventureWorks2008.Sales.SalesOrderDetail
  
  -- Bad reference I meant to type b.ProductID
  UPDATE a
  SET a.TotalDue = a.TotalDue * 1.05
  FROM SOH a
  INNER JOIN SOD b
  ON a.SalesOrderID = b.SalesOrderID
  WHERE c.ProductID != 800
  GO
  /*
  Msg 4104, Level 16, State 1, Line 12
  The multi-part identifier "c.ProductID" could not be bound.
  */
  
  -- Correct reference, successful query
  UPDATE a
  SET a.TotalDue = a.TotalDue * 1.05
  FROM SOH a
  INNER JOIN SOD b
  ON a.SalesOrderID = b.SalesOrderID
  WHERE b.ProductID != 800
  GO
  -- (31366 row(s) affected)
  
  DROP TABLE SOH
  DROP TABLE SOD
  
  

  ---

  Kalman Toth
  New Book:
  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

  • Marked as answer by
    Kalman Toth
    Tuesday, November 15, 2011 9:25 PM
  • Edited by
    Kalman Toth
    Friday, November 10, 2017 10:03 AM

• Mine was a total bonehead mistake.  I mis-spelled the table name in the SELECT field.

  IntelliSense may be helpful (starting with SQL Server 2008):

  ---

  • Marked as answer by
    Kalman Toth
    Tuesday, November 15, 2011 9:24 PM
  • Edited by
    Kalman Toth
    Thursday, November 9, 2017 1:08 AM

• • Proposed as answer by
    Naomi N
    Tuesday, December 14, 2010 7:16 PM
  • Marked as answer by
    Kalman Toth
    Wednesday, March 30, 2011 1:07 AM
  • Edited by
    Kalman Toth
    Thursday, November 9, 2017 1:08 AM