February 17, 2010 at 4:56 pm
I am getting the error multi-part.. pulling my hair out...:(
USE [DBCrew]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE dbo.SearchCrewAdvanced
(
@ZipStart char(5) = Null,
@Distance int = Null,
@PositionID int = Null,
@MinWeight varchar(50) = Null,
@MaxWeight varchar(50) = Null,
@EventID int = Null,
@BoatID int = Null,
@EventResults varchar(2000) = Null
)
AS
IF @ZipStart IS NULL
BEGIN
SELECT tblUser.* ,
tblAddress.* ,
tblPhone.* ,
tblExperience.*
FROM tbl_Users tblUser with (nolock)
JOIN tbl_Address tblAddress with (nolock)
On tblUser.UserID = tblAddress.UserId
JOIN tbl_Expereince tblExperience with (nolock)
On tblExperience.UserID = tblUser.UserID
JOIN tbl_Physical tblPhysical with (nolock)
On tblPhysical.UserID = tblUser.UserID
JOIN tbl_phone tblPhone
On tblPhone.UserID = tblUser.UserID
WHERE
(tblExperience.EventID = ISNULL(@EventID, tblExperience.EventID)) AND
-- (Job.ArtistID = ISNULL(@BoatID, Job.ArtistID)) AND
(tblPhysical.Weight >= ISNULL(@MinWeight, tblPhysical.Weight)) AND
(tblPhysical.Weight <= ISNULL(@MaxWeight, tblPhysical.Weight)) AND
(tblExperience.PositionID = ISNULL(@PositionID, tblExperience.PositionID)) AND
(tblExperience.BoatID = ISNULL(@EventID, tblExperience.BoatID)) AND
-- (tblUser.UserSex = ISNULL(@UserSex, tblUser.UserSex)) AND
(tblExperience.EventResults LIKE ISNULL(@EventResults, tblExperience.EventResults) + '%')
ORDER BY tblUser.UserLastName, tblUser.UserFirstName
END
ELSE
BEGIN
Create Table #tmpZipCode
(
ZipCode varchar(5)
,Distance decimal(18,2)
)
Insert Into #tmpZipCode
SELECT Distinct zc2.ZipCode, (blabhlabhlabh Distance calculation) As Distance
FROM dbo.tbl_ZipCode zc1,
dbo.tbl_ZipCode zc2
WHERE zc1.ZipCode = @ZipStart
AND blah blahb blah <= @Distance
--Declare @strSQL varchar(8000)
SELECT
tblUser.*,
tblAddress.*,
tblPhone.*,
tblExperience.*
FROM tbl_Address tblAddress with (nolock)
JOIN #tmpZipCode zc
On tblAddress.AddressZipCode = zc.ZipCode
JOIN tbl_Users tblUser with (nolock)
On tblUser.UserID = tblAddress.UserId
JOIN tbl_Expereince tblExperience with (nolock)
On tblExperience.UserID = tblUser.UserID
JOIN tbl_Physical tblPhysical with (nolock)
On tblPhysical.UserID = tblUser.UserID
JOIN tbl_phone tblPhone
On tblPhone.UserID = tblUser.UserID
WHERE
(tEx.BoatID = ISNULL(@BoatID, tEx.BoatID) Or ISNULL(@BoatID, tEx.BoatID) Is Null) AND
-- (Job.ArtistID = ISNULL(@BoatID, Job.ArtitEx.BoatID = '+ ISNULL(@BoatID, tEx.BoatID) +' OrstID)) AND
(tblPhysical.Weight >= ISNULL(@MinWeight, tblPhysical.Weight) Or ISNULL(@MinWeight, tblPhysical.Weight) Is Null) AND
(tblPhysical.Weight <= ISNULL(@MaxWeight, tblPhysical.Weight) Or ISNULL(@MaxWeight, tblPhysical.Weight) Is Null) AND
(tblExperience.PositionID = ISNULL(@PositionID, tblExperience.PositionID) Or ISNULL(@PositionID, tblExperience.PositionID) Is Null) AND
(tblExperience.BoatID = ISNULL(@EventID, tblExperience.BoatID) Or ISNULL(@EventID, tblExperience.BoatID) Is Null) AND
-- (tblUser.UserSex = ISNULL(@UserSex, tblUser.UserSex)) AND
(tblExperience.EventResults LIKE ISNULL(@EventResults, tblExperience.EventResults) + '%')
ORDER BY zc.Distance, tblUser.UserLastName, tblUser.UserFirstName
END
Msg 4104, Level 16, State 1, Procedure SearchCrewAdvanced, Line 66
The multi-part identifier "tEx.BoatID" could not be bound.
Msg 4104, Level 16, State 1, Procedure SearchCrewAdvanced, Line 66
The multi-part identifier "tEx.BoatID" could not be bound.
Msg 4104, Level 16, State 1, Procedure SearchCrewAdvanced, Line 66
The multi-part identifier "tEx.BoatID" could not be bound.
Please help.
Thanks in advanced
February 17, 2010 at 6:03 pm
FYI: I think that you used a [ quote ] tag in your post when you meant to use a [ code="sql" ] tag.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 17, 2010 at 6:06 pm
Give us the whole error message please. Also we need a version of your query that can reproduce the problem. Having "blah blahblah" in there makes it impossible for us to find it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 18, 2010 at 9:36 am
Hello Thank you for the reply.
USE [DBCrew]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE dbo.SearchCrewAdvanced
(
@ZipStart char(5) = Null,
@Distance int = 10,
@PositionID int = Null,
@MinWeight varchar(50) = Null,
@MaxWeight varchar(50) = Null,
@EventID int = Null,
@BoatID int = Null,
@EventResults varchar(2000) = Null
)
AS
IF @ZipStart IS NULL
BEGIN
SELECT tblUser.* ,
tblAddress.* ,
tblPhone.* ,
tblExperience.*
FROM tbl_Users tblUser with (nolock)
JOIN tbl_Address tblAddress with (nolock)
On tblUser.UserID = tblAddress.UserId
JOIN tbl_Expereince tblExperience with (nolock)
On tblExperience.UserID = tblUser.UserID
JOIN tbl_Physical tblPhysical with (nolock)
On tblPhysical.UserID = tblUser.UserID
JOIN tbl_phone tblPhone
On tblPhone.UserID = tblUser.UserID
WHERE
(tblExperience.EventID = ISNULL(@EventID, tblExperience.EventID)) AND
-- (Job.ArtistID = ISNULL(@BoatID, Job.ArtistID)) AND
(tblPhysical.Weight >= ISNULL(@MinWeight, tblPhysical.Weight)) AND
(tblPhysical.Weight <= ISNULL(@MaxWeight, tblPhysical.Weight)) AND
(tblExperience.PositionID = ISNULL(@PositionID, tblExperience.PositionID)) AND
(tblExperience.BoatID = ISNULL(@EventID, tblExperience.BoatID)) AND
-- (tblUser.UserSex = ISNULL(@UserSex, tblUser.UserSex)) AND
(tblExperience.EventResults LIKE ISNULL(@EventResults, tblExperience.EventResults) + '%')
ORDER BY tblUser.UserLastName, tblUser.UserFirstName
END
ELSE
BEGIN
Create Table #tmpZipCode
(
ZipCode varchar(5)
,Distance decimal(18,2)
)
Insert Into #tmpZipCode
SELECT Distinct Distance
FROM dbo.tbl_ZipCode
WHERE zc1.ZipCode = @ZipStart
AND Distance <= @Distance
-- Declare @strSQL varchar(8000)
SELECT
tblUser.*,
tblAddress.*,
tblPhone.*,
tblExperience.*
FROM tbl_Address tblAddress with (nolock)
JOIN #tmpZipCode zc
On tblAddress.AddressZipCode = zc.ZipCode
JOIN tbl_Users tblUser with (nolock)
On tblUser.UserID = tblAddress.UserId
JOIN tbl_Expereince tblExperience with (nolock)
On tblExperience.UserID = tblUser.UserID
JOIN tbl_Physical tblPhysical with (nolock)
On tblPhysical.UserID = tblUser.UserID
JOIN tbl_phone tblPhone
On tblPhone.UserID = tblUser.UserID
WHERE
(tEx.BoatID = ISNULL(@BoatID, tEx.BoatID) Or ISNULL(@BoatID, tEx.BoatID) Is Null) AND
-- (Job.ArtistID = ISNULL(@BoatID, Job.ArtitEx.BoatID = '+ ISNULL(@BoatID, tEx.BoatID) +' OrstID)) AND
(tblPhysical.Weight >= ISNULL(@MinWeight, tblPhysical.Weight) Or ISNULL(@MinWeight, tblPhysical.Weight) Is Null) AND
(tblPhysical.Weight <= ISNULL(@MaxWeight, tblPhysical.Weight) Or ISNULL(@MaxWeight, tblPhysical.Weight) Is Null) AND
(tblExperience.PositionID = ISNULL(@PositionID, tblExperience.PositionID) Or ISNULL(@PositionID, tblExperience.PositionID) Is Null) AND
(tblExperience.BoatID = ISNULL(@EventID, tblExperience.BoatID) Or ISNULL(@EventID, tblExperience.BoatID) Is Null) AND
-- (tblUser.UserSex = ISNULL(@UserSex, tblUser.UserSex)) AND
(tblExperience.EventResults LIKE ISNULL(@EventResults, tblExperience.EventResults) + '%')
ORDER BY zc.Distance, tblUser.UserLastName, tblUser.UserFirstName
END
And Error Message is
Msg 4104, Level 16, State 1, Procedure SearchCrewAdvanced, Line 66
The multi-part identifier "tEx.BoatID" could not be bound.
Msg 4104, Level 16, State 1, Procedure SearchCrewAdvanced, Line 66
The multi-part identifier "tEx.BoatID" could not be bound.
Msg 4104, Level 16, State 1, Procedure SearchCrewAdvanced, Line 66
The multi-part identifier "tEx.BoatID" could not be bound.
February 18, 2010 at 9:46 am
I guessing that it's because you have no table nor table alias named "tEx".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 18, 2010 at 9:52 am
Where are you getting tex.BoatID from?? which table is it in?? you should alias some table with tex, the error message says there is no column with tex.BoatID.
February 18, 2010 at 9:59 am
I would also guess that this is a "cut and paste" matter, from a query where tblExperience had the alias "tEx".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 18, 2010 at 10:08 am
Sorry I cut & pasted wrong
USE [DBCrew]
GO
/****** Object: StoredProcedure [dbo].[spSearchCrew] Script Date: 02/18/2010 09:02:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSearchCrew] (
@ZipStart char(5),
@Distance int = 10,
@PositionID int = Null,
@MinWeight varchar(50) = Null,
@MaxWeight varchar(50) = Null,
@EventID int = Null,
@BoatID int = Null,
@EventResults varchar(2000) = Null
)
AS
BEGIN
SET NOCOUNT ON;
Create Table #tmpZipCode
(
ZipCode varchar(5)
,Distance decimal(18,2)
)
Insert Into #tmpZipCode
SELECT Distinct zc2.ZipCode, Distance
FROM dbo.tbl_ZipCode zc1
WHERE zc1.ZipCode = @ZipStart
AND Distance= @Distance
Declare @strSQL varchar(8000)
Set @strSQL = 'Select Distinct zc.Distance
,tUsr.*
,tAdd.*
,tEx.*
,tPh.*
From tbl_Address tAdd with (nolock)
Join #tmpZipCode zc
On tAdd.AddressZipCode = zc.ZipCode
Join tbl_Users tUsr with (nolock)
On tUsr.UserID = tAdd.UserId
Join tbl_Expereince tEx with (nolock)
On tEx.UserID = tAdd.UserId
And tEx.PositionID = ' + Cast(@PositionID As varchar(20)) + '
Left Outer Join tbl_Physical tPh with (nolock)
On tPh.UserID = tUsr.UserID
Where (tEx.PositionID = ' + Cast(@PositionID As varchar(20)) + ' Or ' + Cast(@PositionID As varchar(20)) + ' Is Null)
And tUsr.ActiveFlag = 1
And (tEx.BoatID = ' + Cast(@BoatID As varchar(20)) + ' Or ' + Cast(@BoatID As varchar(20)) + ' Is Null)
And (tEx.EventID = ' + Cast(@EventID As varchar(20)) + ' Or ' + Cast(@EventID As varchar(20)) + ' Is Null)
And (tPh.Weight >= ' + IsNull(Cast(@MinWeight As varchar(20)), 'Null') + ' Or ' + IsNull(Cast(@MinWeight As varchar(20)), 'Null') + ' Is Null)
And (tPh.Weight <= ' + IsNull(Cast(@MaxWeight As varchar(20)), 'Null') + ' Or ' + IsNull(Cast(@MaxWeight As varchar(20)), 'Null') + ' Is Null)
And (tEx.EventResults = ' + Cast(@EventResults As varchar(2000)) + ' Or ' + Cast(@EventResults As varchar(2000)) + ' Is Null)
Order By zc.Distance'
Exec (@strSQL)
END
Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Or'.
February 18, 2010 at 10:43 am
Add a line like "PRINT @strSQL", right before the "Exec (@strSQL)" line. Then run the procedure again and post the PRINT output for us.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 18, 2010 at 10:59 am
Hello,
Thank you for your help.
Select Distinct zc.Distance
,tUsr.*
,tAdd.*
,tEx.*
,tPh.*
From tbl_Address tAdd with (nolock)
Join #tmpZipCode zc
On tAdd.AddressZipCode = zc.ZipCode
Join tbl_Users tUsr with (nolock)
On tUsr.UserID = tAdd.UserId
Join tbl_Expereince tEx with (nolock)
On tEx.UserID = tAdd.UserId
And tEx.PositionID = 0
Left Outer Join tbl_Physical tPh with (nolock)
On tPh.UserID = tUsr.UserID
Where (tEx.PositionID = 0 Or 0 Is Null)
And tUsr.ActiveFlag = 1
And (tEx.BoatID = 0 Or 0 Is Null)
And (tEx.EventID = 0 Or 0 Is Null)
And (tPh.Weight >= Or Is Null)
And (tPh.Weight <= Or Is Null)
And (tEx.EventResults = Or Is Null)
Order By zc.Distance
Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Or'.
February 18, 2010 at 1:49 pm
These last few lines of your dynamic query:
...
And (tEx.BoatID = 0 Or 0 Is Null)
And (tEx.EventID = 0 Or 0 Is Null)
And (tPh.Weight >= Or Is Null)
And (tPh.Weight <= Or Is Null)
And (tEx.EventResults = Or Is Null)
Order By zc.Distance
You have 5 AND clauses with an "OR" in them. The first two end with "Or 0 Is Null", which is valid, but on the last three the zero gets lost: "Or Is Null" which is not valid syntax.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 18, 2010 at 2:53 pm
so this is a SQL limitation?
February 18, 2010 at 3:05 pm
RBarryYoung (2/18/2010)
These last few lines of your dynamic query:
...
And (tEx.BoatID = 0 Or 0 Is Null)
And (tEx.EventID = 0 Or 0 Is Null)
And (tPh.Weight >= Or Is Null)
And (tPh.Weight <= Or Is Null)
And (tEx.EventResults = Or Is Null)
Order By zc.Distance
You have 5 AND clauses with an "OR" in them. The first two end with "Or 0 Is Null", which is valid, but on the last three the zero gets lost: "Or Is Null" which is not valid syntax.
A little more than that is missing, Barry:
...
And (tEx.BoatID = 0 Or 0 Is Null)
And (tEx.EventID = 0 Or 0 Is Null)
And (tPh.Weight >= ? Or Is Null)
And (tPh.Weight <= ? Or Is Null)
And (tEx.EventResults = ? Or Is Null)
Order By zc.Distance
Notice the ? I added?
February 18, 2010 at 3:09 pm
Aloha (2/18/2010)
so this is a SQL limitation?
No - the syntax coming out of your dynamic SQL is incorrect. correct syntax is
[somecolumn] = somevalue or [somecolumn] is null
You can't skip parts of those clauses without that becoming an issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 18, 2010 at 3:40 pm
Changed to:
@MinWeight money = Null,
@MaxWeight money = Null,
and Print
Select Distinct zc.Distance
,tUsr.*
,tAdd.*
,tEx.*
,tPh.*
From tbl_Address tAdd with (nolock)
Join #tmpZipCode zc
On tAdd.AddressZipCode = zc.ZipCode
Join tbl_Users tUsr with (nolock)
On tUsr.UserID = tAdd.UserId
Join tbl_Expereince tEx with (nolock)
On tEx.UserID = tAdd.UserId
And tEx.PositionID = 1
Left Outer Join tbl_Physical tPh with (nolock)
On tPh.UserID = tUsr.UserID
Where (tEx.PositionID = 1 Or 1 Is Null)
And tUsr.ActiveFlag = 1
And (tEx.BoatID = 0 Or 0 Is Null)
And (tEx.EventID = 0 Or 0 Is Null)
And (tPh.Weight >= 0.00 Or 0.00 Is Null)
And (tPh.Weight <= 0.00 Or 0.00 Is Null)
And (tEx.EventResults LIKE %)
Order By zc.Distance
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply