Solved

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

Posted on 2006-07-04
3
1,433 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
Comment Utility
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
Comment Utility
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
Comment Utility
Its ok. I just had to replace the 7s with +s

Many thanks!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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 …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 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

12 Experts available now in Live!

Get 1:1 Help Now