Multi-part identifier could not be bound

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

Multi-part identifier could not be bound

Author

Topic

iand109
Starting Member

14 Posts

Posted - 2009-10-16 : 10:04:29

Hi,
I have a query which is like this:

IF Staff.dbo.staff.KnownAsName IS NOT NULL
BEGIN
SELECT DISTINCT
dbo.STAFF.FORENAME + ' ' + dbo.STAFF.SURNAME AS Name, Employment_1.EmpEnd, Employment_1.EmpStart, dbo.STAFF.Sex, dbo.STAFF.Manager,
dbo.STAFF.EmployeeNo, dbo.STAFF.Profile, dbo.STAFF.Qual1, dbo.STAFF.Qual2, dbo.STAFF.Qual3, dbo.STAFF.Qual4, dbo.STAFF.Qual5,
dbo.STAFF.Qual6, dbo.STAFF.Qual7, dbo.STAFF.Qual8, dbo.STAFF.Qual9, dbo.STAFF.Qual10, dbo.STAFF.SupressScores,
dbo.STAFF.LastScoreSheetPrinted, dbo.STAFF.UID, Employment_1.ID, Employment_1.Centre, Employment_1.Position, dbo.STAFF.Photograph,
(SELECT MIN(EmpStart) AS Expr1
FROM dbo.Employment
WHERE (dbo.STAFF.AppNo = AppNo)
GROUP BY AppNo) AS EarliestStartDate
FROM dbo.Employment AS Employment_1 LEFT OUTER JOIN
dbo.STAFF ON Employment_1.AppNo = dbo.STAFF.AppNo
WHERE (Employment_1.EmpEnd IS NULL) AND (Employment_1.EmpStart < GETDATE()) OR
(Employment_1.EmpEnd > GETDATE())
END
ELSE
BEGIN
SELECT DISTINCT
dbo.STAFF.FORENAME + ' ' + dbo.STAFF.SURNAME AS Name, Employment_1.EmpEnd, Employment_1.EmpStart, dbo.STAFF.Sex, dbo.STAFF.Manager,
dbo.STAFF.EmployeeNo, dbo.STAFF.Profile, dbo.STAFF.Qual1, dbo.STAFF.Qual2, dbo.STAFF.Qual3, dbo.STAFF.Qual4, dbo.STAFF.Qual5,
dbo.STAFF.Qual6, dbo.STAFF.Qual7, dbo.STAFF.Qual8, dbo.STAFF.Qual9, dbo.STAFF.Qual10, dbo.STAFF.SupressScores,
dbo.STAFF.LastScoreSheetPrinted, dbo.STAFF.UID, Employment_1.ID, Employment_1.Centre, Employment_1.Position, dbo.STAFF.Photograph,
(SELECT MIN(EmpStart) AS Expr1
FROM dbo.Employment
WHERE (dbo.STAFF.AppNo = AppNo)
GROUP BY AppNo) AS EarliestStartDate
FROM dbo.Employment AS Employment_1 LEFT OUTER JOIN
dbo.STAFF ON Employment_1.AppNo = dbo.STAFF.AppNo
WHERE (Employment_1.EmpEnd IS NULL) AND (Employment_1.EmpStart < GETDATE()) OR
(Employment_1.EmpEnd > GETDATE())

END

but when I verify the syntax, i get the Message 'multi-part identifier 'staff.dbo.staff.KnownAsName' could not be bound.
Please can anyone give me any clues as to how to resolve this issue?
thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 10:09:59

IF Staff.dbo.staff.KnownAsName IS NOT NULL

without select and from not possible.

No, you're never too old to Yak'n'Roll if you're too young to die.

iand109
Starting Member

14 Posts

Posted - 2009-10-16 : 10:25:10

Hi,
thanks for the reply.
So how would I construct it if I want to merge two columns: If KnownAsName isn't empty, then I want to merge ('KnownAsName' and 'Surname') as Name. If KnownAsName is empty, then I want to merge 'forename' and 'surname' as Name - all within the above select statement.
Many Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 10:35:45

select
coalesce(KnownAsName, forename) + ' ' + Surname as Name,
...
from ...

No, you're never too old to Yak'n'Roll if you're too young to die.

iand109
Starting Member

14 Posts

Posted - 2009-10-16 : 10:39:39

Thank you, thank you. That works just fine. Thanks again.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 11:04:00