sql 查询报错
为什么下面这段sql会报这个错？不解。。。
SELECT tblInstances.Entity, tblJobs.JobNo, tblProspects.Instance, tblProspects.JobNo, tblProspects.CustomerNo,
tblProspects.CustomerName, tblEmployees.[Name] AS ProjMgr, tblProspectDetails.ContractCurrency, tblProspects.DueDate AS [booking date],
tblCustomers.Term, tblCustomers.CreditStatus, tblProspects.ExchRate, tblProspects.EndUserName, tblJobs.Status,
tblJobs.RevenueMethod, tblProspectDetails.HPSCrystalID, tblProspectDetails.HPSEGAPRefNo, tblJobs.CompletedDate,
(case when (tbljobs.Status <> 0) Or (Year([CompletedDate])=2007) then 1 else 0 end) AS [Include in DB],
tblProspects.TTP, tblProspects.ProspectNo,tblProspects.BC01 AS [Orig Contract Value], [CC01]+[cc06] AS [Orig Budget],
tblJobs.ExpiryDate, tblJobs.EstCompletionDate,
(case when (tbljobs.Status <> 0) Or (Year([CompletedDate])=2007) then 1 else 0 end) AS Expr1
FROM tblInstances
JOIN tblCustomers ON tblCustomers.CustomerNo = tblProspects.CustomerNo AND tblCustomers.Instance = tblProspects.Instance
JOIN tblJobs ON tblInstances.Instance = tblJobs.Instance
JOIN tblProspects ON tblProspects.JobNo = tblJobs.JobNo AND tblProspects.Instance = tblJobs.Instance
JOIN tblProspectDetails ON (tblProspects.ProspectNo = tblProspectDetails.ProspectNo) AND (tblProspects.Instance = tblProspectDetails.Instance)
JOIN tblEmployees ON tblJobs.Manager = tblEmployees.Username
WHERE tblProspects.TTP= "B " AND (case when ([tbljobs].[Status] <> 0) Or (Year([CompletedDate])=2007) then 1 else 0 end)=1
AND ((tblProspects.JobType)= "pro " Or (tblProspects.JobType)= "con ")
AND ((tblProspects.LOB)= '410 ' Or (tblProspects.LOB)= '430 ' Or ((tblProspects.LOB) Like '7% '))
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'tblProspects ' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'tblProspects ' does not match with a table name or alias name used in the query.
[解决办法]
你看看tblProspects表中的列名写的可对，去掉一些列，一步一步调试看看
[解决办法]
SELECT T1.Entity
, T3.JobNo
, T4.Instance
, T4.JobNo
, T4.CustomerNo
, T4.CustomerName
, T6.[Name] AS ProjMgr
, T5.ContractCurrency
, T4.DueDate AS [booking date]
, T2.Term
, T2.CreditStatus
, T4.ExchRate
, T4.EndUserName
, T3.Status
, T3.RevenueMethod
, T5.HPSCrystalID
, T5.HPSEGAPRefNo
, T3.CompletedDate
, (case when (T3.Status <> 0) Or (Year([CompletedDate])=2007) then 1 else 0 end) AS [Include in DB]
, T4.TTP
, T4.ProspectNo
, T4.BC01 AS [Orig Contract Value]
, [CC01]+[cc06] AS [Orig Budget]
, T3.ExpiryDate
, T3.EstCompletionDate
,(case when (T3.Status <> 0) Or (Year([CompletedDate])=2007) then 1 else 0 end) AS Expr1
FROM tblInstances T1
JOIN tblCustomers T2 ON T2.CustomerNo = T4.CustomerNo AND T2.Instance = T4.Instance
JOIN tblJobs T3 ON T1.Instance = T3.Instance
JOIN tblProspects T4 ON T4.JobNo = T3.JobNo AND T4.Instance = T3.Instance
JOIN tblProspectDetails T5 ON (T4.ProspectNo = T5.ProspectNo) AND (T4.Instance = T5.Instance)
JOIN tblEmployees T6 ON T3.Manager = T6.Username
WHERE T4.TTP= 'B ' AND (case when ([T3].[Status] <> 0) Or (Year([CompletedDate])=2007) then 1 else 0 end)=1
AND ((T4.JobType)= 'pro ' Or (T4.JobType)= 'con ')
AND ((T4.LOB)= '410 ' Or (T4.LOB)= '430 ' Or ((T4.LOB) Like '7% '))
