Solved

The multi-part identifier "ccar_history.in_date" could not be bound.

Posted on 2006-07-04
3
1,440 Views
Last Modified: 2010-08-05
Hi

Im currently faced with the mammoth task of migrating a fairly large web application across from Access to SQL Server and am having a nightmare with the following bit of SQL which is returning the error The multi-part identifier "ccar_history.in_date" could not be bound.

I know theres a lot of SQL to wade through, but if someone could point me in the right direction as to why this error occurs and how to fix it I would be eternally greatful

cheers


SELECT Repairs.Repairs_ID,InsuranceCompanies.insurance_ID,OwnersCar.car_id,OwnersCar.mobile AS custMobile,Repairs.stage,Repairs.customerInformed,Repairs.finished,Repairs.courtesycar_id,Repairs.hirecar,ccar_history.in_date,ccar_history.out_date,CarManafacturers.Manafacturer & ' ' & CarModels.Model AS [Vehicle],ownerscar.title & ' ' & OwnersCar.ownername AS [Owner],OwnersCar.carreg AS [Reg],Repairs.JobRefNo AS [JobRef],CourtesyCars.carreg AS [CC],Repairs.startDate AS [Start],InsuranceCompanies.Name AS [Provider],StatusCodes.Description AS [Status],Repairs.estfinishdate AS [Finish],Repairs.HitCount AS [Hit count],OwnersCar.claimnumber AS [ClaimNo]
    FROM (CarManafacturers INNER JOIN (((OwnersCar INNER JOIN (InsuranceCompanies INNER JOIN (CarModels INNER JOIN (StatusCodes INNER JOIN Repairs ON StatusCodes.Status_ID = Repairs.stage) ON CarModels.make_id = Repairs.make_ID) ON InsuranceCompanies.insurance_ID = Repairs.insurance_ID) ON OwnersCar.car_ID = Repairs.cars_ID) LEFT JOIN (SELECT CourtesyCars.carreg,ccar_history.ccar_history_id,ccar_history.in_date,ccar_history.out_date FROM CourtesyCars INNER JOIN ccar_history ON CourtesyCars.courtesycar_id = ccar_history.ccar_id) CourtesyCars ON Repairs.courtesyCar_ID = CourtesyCars.ccar_history_id) LEFT JOIN Operatives ON Repairs.Op_ID = Operatives.Op_Id) ON CarManafacturers.Manafacturers_id = CarModels.Manafacturer_id) INNER JOIN colours ON OwnersCar.colour_id = colours.colour_id
    WHERE 1=1 AND Potential = 0 AND Repairs.finished=0 AND Repairs.StartDate <= Getdate()
0
Comment
Question by:digital_soul
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 125 total points
ID: 17037916
yes you'd lost them because you'd re-aliased them as courtesycar  not courtestcarhistory...

SELECT R.Repairs_ID
,IC.insurance_ID
,OC.car_id
,OC.mobile AS custMobile
,R.stage
,R.customerInformed
,R.finished
,R.courtesycar_id
,R.hirecar
,cc.in_date
,cc.out_date
,CMan.Manafacturer & ' ' & CMod.Model AS [Vehicle]
,OC.title & ' ' & OC.ownername AS [Owner]
,OC.carreg AS [Reg]
,R.JobRefNo AS [JobRef]
,cc.carreg AS [CC]
,R.startDate AS [Start]
,IC.Name AS [Provider]
,StatusCodes.Description AS [Status]
,R.estfinishdate AS [Finish]
,R.HitCount AS [Hit count]
,OC.claimnumber AS [ClaimNo]
    FROM Repairs as R
INNER JOIN InsuranceCompanies as IC
   ON IC.insurance_ID = R.insurance_ID
INNER JOIN CarModels as CMod
   ON CMod.make_id = R.make_ID
Inner Join CarManafacturers as CMan
   ON CMan.Manafacturers_id = CMod.Manafacturer_id
INNER JOIN StatusCodes
   ON StatusCodes.Status_ID = R.stage
INNER JOIN OwnersCar as OC
   ON OC.car_ID = R.cars_ID
INNER JOIN colours as Col
   ON OC.colour_id = col.colour_id

 LEFT JOIN (SELECT cc.carreg,cch.ccar_history_id
                  ,cch.in_date,cch.out_date
             FROM CourtesyCars as cc
            INNER JOIN ccar_history as cch
               ON cc.courtesycar_id = cch.ccar_id) as CC
   ON R.courtesyCar_ID = cc.ccar_history_id

 LEFT JOIN Operatives as OP
   ON R.Op_ID = OP.Op_Id


    WHERE 1=1
AND Potential = 0
AND R.finished=0
AND R.StartDate <= Getdate()

0
 
LVL 2

Author Comment

by:digital_soul
ID: 17040984
Great that makes sense, however I am now getting the following error:

The datatypes NVarChar and VarChar are incompatible in the boolean AND operator

Is this something wrong with the query or my database setup?
0
 
LVL 2

Author Comment

by:digital_soul
ID: 17040991
Its ok. I just had to replace the 7s with +s

Many thanks!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now