Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multi part identifier could not be found

Posted on 2011-09-14
9
Medium Priority
?
308 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:newone2011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 7

Expert Comment

by:twol
ID: 36539501
Is there a field in the ORDER_TYPE table called OrderTypeID?


0
 

Author Comment

by:newone2011
ID: 36539549
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36539734
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:newone2011
ID: 36539804
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36539827
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
 

Author Comment

by:newone2011
ID: 36539840
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36539886
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
 

Author Comment

by:newone2011
ID: 36539898
Seems to work, I will test more and award points by tomorrow. Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36539932
Okay, sounds good!
Best regards and happy coding,

Kevin
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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