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?
 
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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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