Solved

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

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

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 42
SQL Server query - can a #temp table be improved with indexing? 6 47
Error when creating an UPDATE Trigger in SQL 6 24
SQL R 21 29
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

820 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