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

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

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
digital_soul
Asked:
digital_soul
  • 2
1 Solution
 
LowfatspreadCommented:
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
 
digital_soulAuthor Commented:
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
 
digital_soulAuthor Commented:
Its ok. I just had to replace the 7s with +s

Many thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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