Solved

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

Posted on 2006-07-04
3
1,437 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

914 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

20 Experts available now in Live!

Get 1:1 Help Now