Getting multi-part identifier could not be be boun

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting multi-part identifier could not be be boun

Author

Topic

siuc1996
Starting Member

6 Posts

Posted - 2010-04-16 : 11:47:00

Getting the following error message:

The multi-part identifier "budHREEmployeeHistory.Date Started" could not be bound.

Here is my work:

select bHRDT.HRRef as [HRRef], bHRDT.JCCo as [JCCo], bHRRM.LastName as [LastName], bHRRM.FirstName as [FirstName], bHRRM.MiddleName as [MiddleName],budHREEmployeeHistory.DateStarted as [DateStarted], max(bHRDT.Date) as [Date]

FROM bHRRM (Nolock)
LEFT JOIN bHRDT (Nolock) on bHRDT.HRCo = bHRRM.HRCo and bHRDT.HRRef = bHRRM.HRRef
JOIN budHREmployeeHistory on bHRDT.HRRef=budHREmployeeHistory.Emp
where bHRDT.JCCo='1' and bHRRM.ActiveYN='Y' and budHREmployeeHistory.DateStarted > '2010-01-01 00:00:00' and budHREmployeeHistory.DateDischarged is null
group by bHRDT.JCCo, bHRDT.HRRef, bHRRM.LastName,bHRRM.FirstName, bHRRM.MiddleName

Can anyone help?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-16 : 12:01:25

Could be a lot of things.

Here's your code with a little formatting


select
	bHRDT.HRRef as [HRRef]
	, bHRDT.JCCo as [JCCo]
	, bHRRM.LastName as [LastName]
	, bHRRM.FirstName as [FirstName]
	, bHRRM.MiddleName as [MiddleName]
	, budHREEmployeeHistory.DateStarted as [DateStarted]
	, max(bHRDT.Date) as [Date]

FROM
	bHRRM (Nolock)
	LEFT JOIN bHRDT (Nolock) on
		bHRDT.HRCo = bHRRM.HRCo
		and bHRDT.HRRef = bHRRM.HRRef

	JOIN budHREmployeeHistory on bHRDT.HRRef = budHREmployeeHistory.Emp
where
	bHRDT.JCCo='1'
	and bHRRM.ActiveYN='Y'
	and budHREmployeeHistory.DateStarted > '2010-01-01 00:00:00'
	and budHREmployeeHistory.DateDischarged is null 
group by
	bHRDT.JCCo
	, bHRDT.HRRef
	, bHRRM.LastName
	, bHRRM.FirstName
	, bHRRM.MiddleName

Its more polite to post code inside code tags the word code in square brackets. check my post by quoting to see how it's done.

Your error says:

budHREEmployeeHistory.Date Started

(note the space) but I can't see a space in your code.

Is it possible that the schema is case sensitive? What column headers are returned (Exactly) by the following sql


SELECT
    [column_Name]
FROM
    information_schema.columns
WHERE
    [table_name] = 'budHREEmployeeHistory'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

siuc1996
Starting Member

6 Posts

Posted - 2010-04-16 : 13:18:01

Actually I was able to figure this one out. I had is spelled as budHREEmployeeHistory when it should have been budHREmployeeHistory. As soon as I took out the extra E, it worked.

But I am having another issue. I am using the max function on the date so that it pulls in the last drug test date. However, it doesn't work properly. If someone has drug test dates of 4/13/09 and 3/31/10, it should pull in 3/31/10. But it pulls 4/13/09 instead. I assume it is because 4 is higher than 3. Is there a better way to handle this?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-16 : 13:50:25

How is March 31, 2010 greater than April 13, 2010? Do you want to use MIN instead of MAX?

siuc1996
Starting Member

6 Posts

Posted - 2010-04-16 : 15:10:57

I think I figured this one out too. I was using this:

max(convert(nvarchar(8),bHRDT.Date,1) as [Date]

So it was converting it to a number and that is why the max did not work properly. Is there any way to convert the date to MM/DD/YY and still allow it to be a date field?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-16 : 16:50:07

Is bHRDt not a date format? If it is, why bother with the convert? Simply max(bHRDt) will do nicely. Or am I overly simplyifying things?

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

siuc1996
Starting Member

6 Posts

Posted - 2010-04-16 : 17:03:32

But I want it to look like MM/DD/YY and if I don't do anything with it, it looks like this: Apri 13 2010 12:00 AM.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 04:05:57

You should format it in your front end app. If you aren't using one then what are you doing with the data? If you are copying / pasting to excel or similar you can set the format of the destination column in excel.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources