The multi-part identifier could not be bound.

I have a query where I am getting messages that these fields cound not be bound.
declare @BeginDate varchar (24)
declare @EndDate varchar (24)

Set @BeginDate = '02/01/2011'
Set @EndDate = '02/10/2011'

Select Lab_Id, TLDCompany, Machine_Id, Sum(Sample_Count)As Sample_Count
From
(SELECT    om.lab_id, om.Company As TLDCompany, om.Machine_ID, sm.Lab_TEST_NUM, accounts.Account_id, accounts.Company, 
          count(Sm.Sample_ID) As Sample_count
 FROM      SPO_MACHINES om  Left outer JOIN
			   SAMPLES sm ON    (sm.LAB_TEST_NUM LIKE om.Machine_ID+'%') AND
                              (om.LAB_ID = accounts.LAB_ID) left outer join
          (Select Sample_id, Account_id 
           From   Samples sm 
           Where (sm.Lab_ID in (Select distinct Lab_Id from OSA_MACHINES ))
                 And (sm.Date_Posted >= @BeginDate
                 And sm.Date_Posted < @EndDate))s on accounts.account_id = sm.account_id
Group by om.lab_id, om.Company, om.Machine_ID, sm.LAB_TEST_NUM, accounts.Account_id, accounts.Company) Sample_counts
Group By Lab_Id, TLDCompany, Machine_Id
order by Lab_Id, TLDCompany, Machine_Id

Open in new window


and these are the messages:
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "accounts.LAB_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "accounts.account_id" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "accounts.Account_id" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "accounts.Company" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "accounts.Account_id" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "accounts.Company" could not be bound.

Open in new window


Any ideas on what I am doing wrong?
zberg007Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ephraim WangoyaCommented:

Prefix all your field names with the table name
0
hyphenpipeCommented:
I do not see anywhere in this query where you have a table called accounts or are aliasing on as such.

Is this the whole query?

Did it work before?
0
hyphenpipeCommented:
Try this:


declare @BeginDate varchar (24)
declare @EndDate varchar (24)

Set @BeginDate = '02/01/2011'
Set @EndDate = '02/10/2011'

Select Lab_Id, TLDCompany, Machine_Id, Sum(Sample_Count)As Sample_Count
From
(SELECT    om.lab_id, om.Company As TLDCompany, om.Machine_ID, sm.Lab_TEST_NUM, accounts.Account_id, accounts.Company, 
          count(Sm.Sample_ID) As Sample_count
 FROM      SPO_MACHINES om  Left outer JOIN
			   SAMPLES accounts ON    (sm.LAB_TEST_NUM LIKE om.Machine_ID+'%') AND
                              (om.LAB_ID = accounts.LAB_ID) left outer join
          (Select Sample_id, Account_id 
           From   Samples sm 
           Where (sm.Lab_ID in (Select distinct Lab_Id from OSA_MACHINES ))
                 And (sm.Date_Posted >= @BeginDate
                 And sm.Date_Posted < @EndDate))s on accounts.account_id = sm.account_id
Group by om.lab_id, om.Company, om.Machine_ID, sm.LAB_TEST_NUM, accounts.Account_id, accounts.Company) Sample_counts
Group By Lab_Id, TLDCompany, Machine_Id
order by Lab_Id, TLDCompany, Machine_Id

Open in new window

0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

zberg007Author Commented:
I adjusted the query to this:
 
declare @BeginDate varchar (24)
declare @EndDate varchar (24)

Set @BeginDate = '02/01/2011'
Set @EndDate = '02/10/2011'

Select Lab_Id, TLDCompany, Machine_Id, Sum(Sample_Count)As Sample_Count
From
(SELECT    om.lab_id, om.Company As TLDCompany, om.Machine_ID, sm.Lab_TEST_NUM, ac.Account_id, ac.Company, 
          count(Sm.Sample_ID) As Sample_count
FROM Accounts ac, SPO_MACHINES om  Left outer JOIN
			   SAMPLES sm ON    (sm.LAB_TEST_NUM LIKE om.Machine_ID+'%') AND
                              (om.LAB_ID = sm.LAB_ID) left outer join
          (Select sm.Sample_id, sm.Account_id 
           From   Samples sm 
           Where (sm.Lab_ID in (Select distinct Lab_Id from SPO_MACHINES ))
                 And (sm.Date_Posted >= @BeginDate
                 And sm.Date_Posted < @EndDate))SAMPLES on ac.account_id = sm.account_id
Group by om.lab_id, om.Company, om.Machine_ID, sm.LAB_TEST_NUM, ac.Account_id, ac.Company) Sample_counts
Group By Lab_Id, TLDCompany, Machine_Id
order by Lab_Id, TLDCompany, Machine_Id

Open in new window


and now I get this for a message:
 
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "ac.account_id" could not be bound.

Open in new window


This is the whole query. I am modifying it from an existing that works fine. I can post the existing one that works if helpful.
0
hyphenpipeCommented:
Post the original.

Thanks.
0
zberg007Author Commented:
Here's the original:
declare @BeginDate varchar (24)
declare @EndDate varchar (24)

Set @BeginDate = '03/01/2011'
Set @EndDate = '03/31/2011'

Select Lab_Id, TLDCompany, Machine_Id, Sum(Sample_Count)As Sample_Count
From
(SELECT    om.lab_id, om.Company As TLDCompany, om.Machine_ID, ac.Lab_CNO, ac.Account_id, ac.Company,
          count(Sm.Sample_ID) As Sample_count
FROM      OSA_MACHINES om  Left outer JOIN
               ACCOUNTS ac ON  (ac.LAB_CNO LIKE om.Machine_ID+'%') AND
                             (om.LAB_ID = ac.LAB_ID) left outer join
          (Select Sample_id, Account_id 
           From   Samples sm 
           Where (Sm.Lab_ID in (Select distinct Lab_Id from OSA_MACHINES ))
                 And (SM.Date_Posted >= @BeginDate
                 And SM.Date_Posted < @EndDate))sm on ac.account_id = sm.account_id
Group by om.lab_id, om.Company, om.Machine_ID, ac.Lab_CNO, ac.Account_id, ac.Company) Sample_counts
Group By Lab_Id, TLDCompany, Machine_Id
order by Lab_Id, TLDCompany, Machine_Id

Open in new window

0
hyphenpipeCommented:
What are you trying to accomplish with your update?
0
hyphenpipeCommented:
Try this:
declare @begindate varchar (24)
declare @enddate varchar (24)

set @begindate = '02/01/2011'
set @enddate = '02/10/2011'

select	lab_id
		, tldcompany
		, machine_id
		, sum(sample_count) as sample_count
from
	(
		select	om.lab_id
				, om.company as tldcompany
				, om.machine_id
				, sm.lab_test_num
				, ac.account_id
				, ac.company
				, count(sm.sample_id) as sample_count
		 from accounts ac, spo_machines om
		 left outer join samples sm on (sm.lab_test_num like om.machine_id+'%') and (om.lab_id = sm.lab_id)
		 left outer join (select	sm.sample_id
									, sm.account_id 
						   from   samples sm 
						   where (sm.lab_id in (select distinct lab_id from spo_machines))
		                   and (sm.date_posted >= @begindate
				           and sm.date_posted < @enddate)) samples on samples.account_id = sm.account_id
		  group by om.lab_id, om.company, om.machine_id, sm.lab_test_num, ac.account_id, ac.company) sample_counts
group by lab_id, tldcompany, machine_id
order by lab_id, tldcompany, machine_id

Open in new window

0
zberg007Author Commented:
Wow, that runs without error but the sample count is outputting some real interesting numbers like this:
lab_id  tldcompany      machine_id      sample_count
SPO	Speedco # 301	0101020-R	971570967
SPO	Speedco # 302	9932601-R	2048660388
SPO	Speedco # 303	28903-246	627741045
SPO	Speedco # 304	24703-245	1086976449
SPO	Speedco # 305	24703-244	645128577

Open in new window


Whereas, I need to count the samples like it does for the original... like this:
 
Lab_Id  TLDCompany      Machine_Id      Sample_Count
ANA	Ana Labs	60000	        0
GTI	AlI Habar	12807-28J	0
GTI	Allied Oil	01204-257	0
GTI	APR Energy LLC	O3-27010-045	438
GTI	Armand Couture	33107-307J	0

Open in new window

0
hyphenpipeCommented:
Is the only thing odd about the new one the sample_count numbers?
0
hyphenpipeCommented:
What is the relationship between the accounts table and the spo_machines table?
0
zberg007Author Commented:
Yes, that is the only thing odd about the new one, the sample_count numbers.

The relationship between the accounts table and the OSA_machines table for the original query is the ACCOUNTS.LAB_CNO LIKE OSA_MACHINES.Machine_ID+'%'

However, in the new query, I don't have the luxury of this relationship, rather it is between the SAMPLES table and the SPO_MACHINES table, like this:
SAMPLES.LAB_TEST_NUM LIKE SPO_MACHINES.Machine_ID+'%'

Hope this makes sense!
0
hyphenpipeCommented:
The problem is this line here;

from accounts ac, spo_machines om

Since we are not providing any relationships between these two tables
the end results is the product of both tables.
0
hyphenpipeCommented:
There is no common column between accounts and spo_machines?
0
zberg007Author Commented:
No, but there is between accounts and samplesL

ACCOUNTS.ACCOUNT_ID and
SAMPLES.ACCOUNT_ID

does that help?
0
hyphenpipeCommented:
And the relationship between samples and spo_machines?
0
hyphenpipeCommented:
Try this:


declare @begindate varchar (24)
declare @enddate varchar (24)

set @begindate = '02/01/2011'
set @enddate = '02/10/2011'

select	lab_id
		, tldcompany
		, machine_id
		, sum(sample_count) as sample_count
from
	(
		select	om.lab_id
				, om.company as tldcompany
				, om.machine_id
				, sm.lab_test_num
				, ac.account_id
				, ac.company
				, count(sm.sample_id) as sample_count
		 from accounts ac, spo_machines om
		 left outer join samples sm on (sm.lab_test_num like om.machine_id+'%') and (om.lab_id = sm.lab_id)
		 left outer join (select	sm.sample_id
									, sm.account_id 
						   from   samples sm 
						   where (sm.lab_id in (select distinct lab_id from spo_machines))
		                   and (sm.date_posted >= @begindate
				           and sm.date_posted < @enddate)) samples on samples.account_id = sm.account_id
		  where om.lab_id = sm.lab_id
		  and ac.account_id = sm.account_id
		  group by om.lab_id, om.company, om.machine_id, sm.lab_test_num, ac.account_id, ac.company) sample_counts
group by lab_id, tldcompany, machine_id
order by lab_id, tldcompany, machine_id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zberg007Author Commented:
SAMPLES.lab_test_num LIKE
SPO_MACHINES.machine_id
0
zberg007Author Commented:
Getting closer, but the sample counts are too high.

lab_id  tldcompany      machine_id      sample_count
SPO	Speedco # 301	0101020-R	11398
SPO	Speedco # 302	9932601-R	24036
SPO	Speedco # 303	28903-246	7365
SPO	Speedco # 304	24703-245	12753
SPO	Speedco # 305	24703-244	7569

Open in new window


When I manually run this query:

 
select count(*)
from samples
where lab_test_num like '0101020-R%'
and lab_id = 'SPO'
and date_posted between '02/01/2011' and '02/10/2011'

Open in new window


I get 14 rows returned for the first machine_id of 0101020-R, which is accurate.

0
zberg007Author Commented:
hypenpipe, thanks for all of your efforts here. You did solve my original question of binding the column.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.