Multi part identifier could not be found

Here is the query:
select ct.OrderTypeID,ProductName,sum(OrdersOfferedHalf) Offered,sum(OrdersHandledHalf) Handled,from Order_Type ct, Order_Type_Half_Hour cthh
left outer join cthh on Order_Type_Half_Hour.OrderTypeID = ct.OrderTypeID
where DateTime > '" & Range("testStart") & "' and DateTime < '" & Range("testEnd") &
and ct.OrderTypeID in (" & Range("queryOrderTypes") & ") ' value from excel
group by ct.OrderTypeID, ct.ProductName
order by ct.OrderTypeID

It says  The multi part identifier ct.OrderTypeID could not be found.  Please advise.
newone2011Asked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Okay, please try with the following changes:

select ct.OrderTypeID, ProductName
     /* ... */
from Order_Type ct
left outer join Order_Type_Half_Hour cthh
   on cthh.OrderTypeID = ct.OrderTypeID

where [DateTime] > '9/4/2011' and [DateTime] < dateadd(d,7,'9/4/2011')
and ct.OrderTypeID in (5001 /*...*/)
group by ct.OrderTypeID, ct.ProductName
order by OrderTypeID
0
 
twolCommented:
Is there a field in the ORDER_TYPE table called OrderTypeID?


0
 
newone2011Author Commented:
Yes, there is. It was working in SQL 2000 where I had to remove the old join condition syntax for 2000 and replace with left outer join to make it work on 2005 and then got this error
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Kevin CrossChief Technology OfficerCommented:
You have quotes and ampersands in the query that suggests it is being called from VB or as some other program as a dynamically generated SQL string; therefore, please post the entire code where you are setting the SQL string variable. The issue is likely in another part of the code ... although there are some type-o's in the above; however, I don't want to waste your time on those if it is a copy and paste issue given only partial code is included.
0
 
newone2011Author Commented:
k, here is the entire query:

select ct.OrderTypeID, ProductName, sum(OrdersOfferedHalf) Offered, sum(OrdersHandledHalf) Handled, sum(AbandInterval1)+sum(AbandInterval2)+sum(AbandInterval3)+sum(AbandInterval4)+sum(AbandInterval5)+sum(AbandInterval6)+sum(AbandInterval7)+sum(AbandInterval8)+sum(AbandInterval9)+sum(AbandInterval10)-sum(ServiceLevelAbandHalf) Aband, sum(OrdersRONAToHalf) RONA, sum(ErrorCountToHalf) Err, sum(OrdersAnsweredToHalf) Ans, sum(HandleTimeHalf) HandleTime, sum(TalkTimeHalf) TalkTime, sum(RouterQueueWaitTimeToHalf) QWaitTime, sum(RouterQueueOrdersToHalf) QOrders, sum(ServiceLevelAbandHalf) AbandLTSL from Order_Type ct, Order_Type_Half_Hour cthh left outer join Order_Type_Half_Hour on cthh.OrderTypeID = ct.OrderTypeID where DateTime > '9/4/2011' and DateTime < dateadd(d,7,'9/4/2011') and ct.OrderTypeID in (5001,5003,5010,5012,5013,5015,5030,5032,5033,5035,5036,5037,5038,5040,5042,5043,5044,5045,5047,5048,5050,5051,5052,5053,5054,5055,5056,5057,5058,5059,5060,5061,5062,5063,5064,5065,5066,5004,5006,5362)
group by ct.OrderTypeID, ct.ProductName order by ct.OrderTypeID
0
 
newone2011Author Commented:
This query was working before in 2000 with same code. The old query was. I just need to make it run on 2005: The difference between the 2 queries is highlighted in bold.

select ct.OrderTypeID, ProductName, sum(OrderOfferedHalf) Offered, sum(OrderHandledHalf) Handled, sum(AbandInterval1)+sum(AbandInterval2)+sum(AbandInterval3)+sum(AbandInterval4)+sum(AbandInterval5)+sum(AbandInterval6)+sum(AbandInterval7)+sum(AbandInterval8)+sum(AbandInterval9)+sum(AbandInterval10)-sum(ServiceLevelAbandHalf) Aband, sum(OrderRONAToHalf) RONA, sum(ErrorCountToHalf) Err, sum(OrderAnsweredToHalf) Ans, sum(HandleTimeHalf) HandleTime, sum(TalkTimeHalf) TalkTime, sum(RouterQueueWaitTimeToHalf) QWaitTime, sum(RouterQueueOrderToHalf) QOrder, sum(ServiceLevelAbandHalf) AbandLTSL from Order_Type ct, Order_Type_Half_Hour cthh where DateTime > '9/14/2011' and DateTime < dateadd(d,7,'9/14/2011') and ct.OrderTypeID *= cthh.OrderTypeID and ct.OrderTypeID in (5001,5003,5010,5012,5013,5015,5030,5032,5033,5035,5036,5037,5038,5040,5042,5043,5044,5045,5047,5048,5050,5051,5052,5053,5054,5055,5056,5057,5058,5059,5060,5061,5062,5063,5064,5065,5066,5004,5006,5362)
group by ct.OrderTypeID, ct.ProductName order by ct.OrderTypeID
0
 
Kevin CrossChief Technology OfficerCommented:
In non-ansi SQL world a *= b is a left join construct. So look at http:#a36539827 and you will see in bold what that translate to using ansi SQL joins. Make those changes to your query and see what happens, please. :)
0
 
newone2011Author Commented:
Seems to work, I will test more and award points by tomorrow. Thanks
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, sounds good!
Best regards and happy coding,

Kevin
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.

All Courses

From novice to tech pro — start learning today.