Link to home
Start Free TrialLog in
Avatar of lapucca
lapucca

asked on

Need help with SQL syntax error

Hi, I don't know why I'm getting these syntax error.  The tables and the columns are there and type correctly.  But when I run the stored procedure, I get the folllowing error.
USE [CtsiMembershipProduction]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[GetData]

SELECT      'Return Value' = @return_value

GO

And I get the following in the Message panel
Msg 208, Level 16, State 1, Procedure GetData, Line 6
Invalid object name 'tblApplication'.

(1 row(s) affected)


USE [CtsiMembershipProduction]
GO
/****** Object:  StoredProcedure [dbo].[GetData]    Script Date: 10/16/2008 19:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[GetData]
AS
 
 
SELECT [Id]
      ,[MembershipLevel]
      ,[ProfessionalOrganizations]
      ,[Department]
      ,[Gender]
      ,[Ethnicity]
      ,[Race]
      ,[YearOfBirth]
      ,[Title_id]
      ,[EmailAddress]
      ,[LastName]
      ,[Firstname]
      ,[PhoneNumber]
      ,[Institution]
      ,[MailingAddress]
      ,[OfficeAddress]
      ,[TitleOther]
      ,[PhdTraineeCount]
      ,[MscTraineeCount]
      ,[InstitutionOther]
      ,[MembershipReasonOther]
      ,[RaceOther]
      ,[MailingAddressLine1]
      ,[MailingAddressLine2]
      ,[MailingAddressCity]
      ,[MailingAddressState]
      ,[MailingAddressZip]
      ,[CommunityOrganization]
	, tblDegrees.Name as [Degree 1]
	, dbo.ApplicationResearchInterests.ResearchInterestId as RchChildId--child Rch id
	, dbo.MembershipReasons.Name as [Membership Reason 1]
	, dbo.MembershipServiceRequirements.Name as [Membership Service Requirement 1]
INTO NewApplication
  FROM [dbo].[Applications] as tblApplication, dbo.ApplicationDegrees, 
dbo.[Degrees] as tblDegrees, dbo.ApplicationMembershipReasons, dbo.ApplicationMembershipServiceRequirements,
dbo.ApplicationResearchInterests, dbo.ResearchInterests as tblRschInt, dbo.MembershipReasons,
dbo.MembershipServiceRequirements
inner join (SELECT min([Id]) AS ID, [email] from tblApplication group by [EmailAddress]) AS B
ON B.ID = tblApplication.Id  --filter our duplicate email address record, get the lowest id
Where tblApplication.Id = dbo.ApplicationDegrees.ApplicationId 
and tblApplication.Id = dbo.ApplicationMembershipReasons.ApplicationId
and tblApplication.Id = dbo.ApplicationMembershipServiceRequirements.ApplicationId
and tblApplication.Id = dbo.ApplicationResearchInterests.ApplicationId
and dbo.ApplicationDegrees.ApplicationId = tblDegrees.Id  --Get degree name
and dbo.ApplicationMembershipReasons.MembershipReasonId = dbo.MembershipReasons.Id
and dbo.ApplicationMembershipServiceRequirements.MembershipServiceRequirementId = 
					dbo.MembershipServiceRequirements.Id
 
 
--Add the 4 Interest fields
ALTER TABLE NewApplication ADD [Research Interest 1] VARCHAR(50)
ALTER TABLE NewApplication ADD [Research Interest 2] VARCHAR(50)
ALTER TABLE NewApplication ADD [Research Interest 3] VARCHAR(50)
ALTER TABLE NewApplication ADD [Research Interest 4] VARCHAR(50)
 
--Create the temp table to hold the interests
CREATE TABLE [dbo].[Interest](
	[Name] [varchar](500) NULL
)
 
DECLARE @interestChildId bigint, @parentId bigint
DECLARE @interestName VARCHAR(500)
 
Declare curApplicationRow Cursor FOR
SELECT RchChildId from NewApplication
 
Open curApplicationRow
FETCH NEXT FROM curApplicationRow
INTO @interestChildId
 
WHILE @@FETCH_STATUS =0  --GO THROUGH EACH RECORD
BEGIN
	--The following select will get us the 1st child id for Rsch interest
	INSERT INTO [CtsiMembershipProduction].[dbo].[Interest]([Name])
	SELECT dbo.ResearchInterests.Name AS interestName
	from dbo.ResearchInterests
	WHERE dbo.ResearchInterests.Id = @interestChildId
 
	SELECT @parentId = (
	SELECT dbo.ResearchInterests.ParentResearchInterest_id as parentId
	from dbo.ResearchInterests
	WHERE dbo.ResearchInterests.Id = @interestChildId)
 
 
--INSERT INTO [CtsiMembershipProduction].[dbo].[Interest]([Name])
--     VALUES
--           (interestName)
 
	WHILE(@parentId <> NULL)--USE THE PARENTID TO TRAVERSE TO ITS PARENT NODE UNTIL REACHES ROOT NODE
	BEGIN
--		set @interestChildId = @parentId
--		SELECT dbo.ResearchInterests.Name, dbo.ResearchInterests.ParentResearchInterest_id as parentId
--		from dbo.ResearchInterests
--		WHERE dbo.ResearchInterests.Id = @interestChildId
 
		INSERT INTO [CtsiMembershipProduction].[dbo].[Interest]([Name])
		SELECT dbo.ResearchInterests.Name AS interestName
		from dbo.ResearchInterests
		WHERE dbo.ResearchInterests.Id = @interestChildId
 
		SELECT @parentId = (
		SELECT dbo.ResearchInterests.ParentResearchInterest_id as parentId
		from dbo.ResearchInterests
		WHERE dbo.ResearchInterests.Id = @interestChildId)
 
	END
 
	FETCH NEXT FROM curApplicationRow
	INTO @interestChildId
END
 
--Remove the unwanted child id columns
ALTER TABLE NewApplication DROP RchChildId
DROP TABLE [dbo].[Interest]
CLOSE curApplicationRow
DEALLOCATE curApplicationRow

Open in new window

Avatar of JDEE8297
JDEE8297
Flag of United States of America image
on line 50 you reference the table tblApplication which is the alias name for the table application, I don't have sql in front of me. however I didn't think you could do that, and if that is the case then could be causing your syntax error.
Avatar of dqmq
dqmq
Flag of United States of America image
Line 50 should be:

inner join (SELECT min([Id]) AS ID, [email] from [dbo].[Applications] group by [EmailAddress]) AS B
 
Avatar of jfmador
jfmador
Flag of Canada image
I think that the error is in your inner join

inner join (SELECT min([Id]) AS ID, [email] from tblApplication group by [EmailAddress]) AS B

If I look your query you use [dbo].[applications] as tblApplication after your FROM. The table tblApplication doesn't exists it is why you got a 208 error message, replace your inner join by

inner join (SELECT min([Id]) AS ID, [email] from [dbo].[applications] group by [EmailAddress]) AS B
Avatar of lapucca
lapucca

ASKER

I thought that might be a problem so I modified to fully reference the table name but I'm still gettting the following message just running the sql that modify the stored procedrue.  Very frustrating.

Msg 4104, Level 16, State 1, Procedure GetData, Line 6
The multi-part identifier "dbo.Applications.Id" could not be bound.
Msg 209, Level 16, State 1, Procedure GetData, Line 6
Ambiguous column name 'Id'.
Msg 209, Level 16, State 1, Procedure GetData, Line 15
Ambiguous column name 'EmailAddress'.

USE [CtsiMembershipProduction]
GO
/****** Object:  StoredProcedure [dbo].[GetData]    Script Date: 10/16/2008 19:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[GetData]
AS
 
 
SELECT [Id]
      ,[MembershipLevel]
      ,[ProfessionalOrganizations]
      ,[Department]
      ,[Gender]
      ,[Ethnicity]
      ,[Race]
      ,[YearOfBirth]
      ,[Title_id]
      ,[EmailAddress]
      ,[LastName]
      ,[Firstname]
      ,[PhoneNumber]
      ,[Institution]
      ,[MailingAddress]
      ,[OfficeAddress]
      ,[TitleOther]
      ,[PhdTraineeCount]
      ,[MscTraineeCount]
      ,[InstitutionOther]
      ,[MembershipReasonOther]
      ,[RaceOther]
      ,[MailingAddressLine1]
      ,[MailingAddressLine2]
      ,[MailingAddressCity]
      ,[MailingAddressState]
      ,[MailingAddressZip]
      ,[CommunityOrganization]
	, tblDegrees.Name as [Degree 1]
	, dbo.ApplicationResearchInterests.ResearchInterestId as RchChildId--child Rch id
	, dbo.MembershipReasons.Name as [Membership Reason 1]
	, dbo.MembershipServiceRequirements.Name as [Membership Service Requirement 1]
INTO NewApplication
  FROM [CtsiMembershipProduction].[dbo].[Applications], dbo.ApplicationDegrees, 
dbo.[Degrees] as tblDegrees, dbo.ApplicationMembershipReasons, dbo.ApplicationMembershipServiceRequirements,
dbo.ApplicationResearchInterests, dbo.ResearchInterests as tblRschInt, dbo.MembershipReasons,
dbo.MembershipServiceRequirements
inner join (SELECT min([Id]) AS ID, [EmailAddress] from [CtsiMembershipProduction].[dbo].[Applications] group by [EmailAddress]) AS B
ON B.ID = dbo.Applications.Id  --filter our duplicate email address record, get the lowest id
Where dbo.Applications.Id = dbo.ApplicationDegrees.ApplicationId 
and dbo.Applications.Id = dbo.ApplicationMembershipReasons.ApplicationId
and dbo.Applications.Id = dbo.ApplicationMembershipServiceRequirements.ApplicationId
and dbo.Applications.Id = dbo.ApplicationResearchInterests.ApplicationId
and dbo.ApplicationDegrees.ApplicationId = tblDegrees.Id  --Get degree name
and dbo.ApplicationMembershipReasons.MembershipReasonId = dbo.MembershipReasons.Id
and dbo.ApplicationMembershipServiceRequirements.MembershipServiceRequirementId = 
					dbo.MembershipServiceRequirements.Id

Open in new window

Avatar of jfmador
jfmador
Flag of Canada image
The two ambiguis are in your select statement you have two field named id and emailaddress one in
tblApplication and one in B you must prefixe these field

SELECT [dbo].[Applications].ID,

For the other error it is about the name of the table you do a FROM [CtsiMembershipProduction].[dbo].[Applications] but you are using dbo.applications in the where statement, I suggest you to remove [CtsiMembershipProduction] because you already using a use at the begining of the procedure

Avatar of jfmador
jfmador
Flag of Canada image
try this :

USE [CtsiMembershipProduction]
GO
/****** Object:  StoredProcedure [dbo].[GetData]    Script Date: 10/16/2008 19:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[GetData]
AS
 
 
SELECT dbo.applications.[Id]
      ,[MembershipLevel]
      ,[ProfessionalOrganizations]
      ,[Department]
      ,[Gender]
      ,[Ethnicity]
      ,[Race]
      ,[YearOfBirth]
      ,[Title_id]
      ,dbo.applications.[EmailAddress]
      ,[LastName]
      ,[Firstname]
      ,[PhoneNumber]
      ,[Institution]
      ,[MailingAddress]
      ,[OfficeAddress]
      ,[TitleOther]
      ,[PhdTraineeCount]
      ,[MscTraineeCount]
      ,[InstitutionOther]
      ,[MembershipReasonOther]
      ,[RaceOther]
      ,[MailingAddressLine1]
      ,[MailingAddressLine2]
      ,[MailingAddressCity]
      ,[MailingAddressState]
      ,[MailingAddressZip]
      ,[CommunityOrganization]
      , tblDegrees.Name as [Degree 1]
      , dbo.ApplicationResearchInterests.ResearchInterestId as RchChildId--child Rch id
      , dbo.MembershipReasons.Name as [Membership Reason 1]
      , dbo.MembershipServiceRequirements.Name as [Membership Service Requirement 1]
INTO NewApplication
  FROM dbo.Applications, dbo.ApplicationDegrees,
dbo.[Degrees] as tblDegrees, dbo.ApplicationMembershipReasons, dbo.ApplicationMembershipServiceRequirements,
dbo.ApplicationResearchInterests, dbo.ResearchInterests as tblRschInt, dbo.MembershipReasons,
dbo.MembershipServiceRequirements
inner join (SELECT min([Id]) AS ID, [EmailAddress] from [dbo].[Applications] group by [EmailAddress]) AS B
ON B.ID = dbo.Applications.Id  --filter our duplicate email address record, get the lowest id
Where dbo.Applications.Id = dbo.ApplicationDegrees.ApplicationId
and dbo.Applications.Id = dbo.ApplicationMembershipReasons.ApplicationId
and dbo.Applications.Id = dbo.ApplicationMembershipServiceRequirements.ApplicationId
and dbo.Applications.Id = dbo.ApplicationResearchInterests.ApplicationId
and dbo.ApplicationDegrees.ApplicationId = tblDegrees.Id  --Get degree name
and dbo.ApplicationMembershipReasons.MembershipReasonId = dbo.MembershipReasons.Id
and dbo.ApplicationMembershipServiceRequirements.MembershipServiceRequirementId =
      dbo.MembershipServiceRequirements.Id



Avatar of lapucca
lapucca

ASKER

Thank you jfmador,
It has improved but still getting complaints about the Id field. I've been working on this for hours and getting tired and frustrated.  Appreicate your help very much.
Msg 4104, Level 16, State 1, Procedure GetData, Line 6
The multi-part identifier "dbo.Applications.Id" could not be bound.

USE [CtsiMembershipProduction]
GO
/****** Object:  StoredProcedure [dbo].[GetData]    Script Date: 10/16/2008 19:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[GetData]
AS
 
 
SELECT [dbo].[Applications].[Id]
      ,[MembershipLevel]
      ,[ProfessionalOrganizations]
      ,[Department]
      ,[Gender]
      ,[Ethnicity]
      ,[Race]
      ,[YearOfBirth]
      ,[Title_id]
      ,[dbo].[Applications].[EmailAddress]
      ,[LastName]
      ,[Firstname]
      ,[PhoneNumber]
      ,[Institution]
      ,[MailingAddress]
      ,[OfficeAddress]
      ,[TitleOther]
      ,[PhdTraineeCount]
      ,[MscTraineeCount]
      ,[InstitutionOther]
      ,[MembershipReasonOther]
      ,[RaceOther]
      ,[MailingAddressLine1]
      ,[MailingAddressLine2]
      ,[MailingAddressCity]
      ,[MailingAddressState]
      ,[MailingAddressZip]
      ,[CommunityOrganization]
	, tblDegrees.Name as [Degree 1]
	, dbo.ApplicationResearchInterests.ResearchInterestId as RchChildId--child Rch id
	, dbo.MembershipReasons.Name as [Membership Reason 1]
	, dbo.MembershipServiceRequirements.Name as [Membership Service Requirement 1]
INTO NewApplication
  FROM [dbo].[Applications], dbo.ApplicationDegrees, 
dbo.[Degrees] as tblDegrees, dbo.ApplicationMembershipReasons, dbo.ApplicationMembershipServiceRequirements,
dbo.ApplicationResearchInterests, dbo.ResearchInterests as tblRschInt, dbo.MembershipReasons,
dbo.MembershipServiceRequirements
inner join (SELECT min([Id]) AS Id, [EmailAddress] from [dbo].[Applications] group by [EmailAddress]) AS B
ON B.Id = [dbo].[Applications].Id  --filter our duplicate email address record, get the lowest id
Where dbo.Applications.Id = dbo.ApplicationDegrees.ApplicationId 
and dbo.Applications.Id = dbo.ApplicationMembershipReasons.ApplicationId
and dbo.Applications.Id = dbo.ApplicationMembershipServiceRequirements.ApplicationId
and dbo.Applications.Id = dbo.ApplicationResearchInterests.ApplicationId
and dbo.ApplicationDegrees.ApplicationId = tblDegrees.Id  --Get degree name
and dbo.ApplicationMembershipReasons.MembershipReasonId = dbo.MembershipReasons.Id
and dbo.ApplicationMembershipServiceRequirements.MembershipServiceRequirementId = 
					dbo.MembershipServiceRequirements.Id

Open in new window

Avatar of aognenoff
aognenoff
Flag of United States of America image
The ambiguous columns name errors are coming from the fact that you are not qualifying what table the attributes you reference in your SELECT clause are coming from.

Example:

Line 13, you should qualify which table [Id] is coming from such as [ table_name].[Id]

As a side note, I would seriously consider doing INNER JOINs in your FROM clause on all those tables rather than the huge amount of implicit CROSS JOINs you're doing.  It'll speed up your query.
Avatar of lapucca
lapucca

ASKER

Ok, I remove the inner join and execute to alter the stored procedure and this time it worked.  But I'm not able to pinpoint where is the bug in the following 2 lines of code.

inner join (SELECT min([Id]) AS Id, [EmailAddress] from [dbo].[Applications] group by [EmailAddress]) AS B
ON B.Id = [dbo].[Applications].Id  --filter our duplicate email address record, get the lowest id
Avatar of jfmador
jfmador
Flag of Canada image
ok then try using an alias for you table dbo.applications

Try this example

SELECT app.[Id]
      ,[MembershipLevel]
      ,[ProfessionalOrganizations]
      ,[Department]
      ,[Gender]
      ,[Ethnicity]
      ,[Race]
      ,[YearOfBirth]
      ,[Title_id]
      ,app.[EmailAddress]
      ,[LastName]
      ,[Firstname]
      ,[PhoneNumber]
      ,[Institution]
      ,[MailingAddress]
      ,[OfficeAddress]
      ,[TitleOther]
      ,[PhdTraineeCount]
      ,[MscTraineeCount]
      ,[InstitutionOther]
      ,[MembershipReasonOther]
      ,[RaceOther]
      ,[MailingAddressLine1]
      ,[MailingAddressLine2]
      ,[MailingAddressCity]
      ,[MailingAddressState]
      ,[MailingAddressZip]
      ,[CommunityOrganization]
      , tblDegrees.Name as [Degree 1]
      , dbo.ApplicationResearchInterests.ResearchInterestId as RchChildId--child Rch id
      , dbo.MembershipReasons.Name as [Membership Reason 1]
      , dbo.MembershipServiceRequirements.Name as [Membership Service Requirement 1]
INTO NewApplication
  FROM dbo.Applications app, dbo.ApplicationDegrees,
dbo.[Degrees] as tblDegrees, dbo.ApplicationMembershipReasons, dbo.ApplicationMembershipServiceRequirements,
dbo.ApplicationResearchInterests, dbo.ResearchInterests as tblRschInt, dbo.MembershipReasons,
dbo.MembershipServiceRequirements
inner join (SELECT min([Id]) AS ID, [EmailAddress] from [dbo].[Applications] group by [EmailAddress]) AS B
ON B.ID = app.Id  --filter our duplicate email address record, get the lowest id
Where app.Id = dbo.ApplicationDegrees.ApplicationId
and app.Id = dbo.ApplicationMembershipReasons.ApplicationId
and app.Id = dbo.ApplicationMembershipServiceRequirements.ApplicationId
and app.Id = dbo.ApplicationResearchInterests.ApplicationId
and dbo.ApplicationDegrees.ApplicationId = tblDegrees.Id  --Get degree name
and dbo.ApplicationMembershipReasons.MembershipReasonId = dbo.MembershipReasons.Id
and dbo.ApplicationMembershipServiceRequirements.MembershipServiceRequirementId =
      dbo.MembershipServiceRequirements.Id
Avatar of lapucca
lapucca

ASKER

Hi Imfador, I copied your code over ,execute the query to update the stored procedure and got the following meesage
Msg 4104, Level 16, State 1, Procedure GetData, Line 5
The multi-part identifier "app.Id" could not be bound.
ASKER CERTIFIED SOLUTION
Avatar of jfmador
jfmador
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of aognenoff
aognenoff
Flag of United States of America image
Try this on for size.  I rewrote your logic using INNER JOINs instead of huge CROSS JOINs with that huge WHERE clause.  Not having your tables or data to play with, I couldn't test it but I think it should work.
SELECT a.Id
	,a.MembershipLevel
	,a.ProfessionalOrganizations
	,a.Department
	,a.Gender
	,a.Ethnicity
	,a.Race
	,a.YearOfBirth
	,a.Title_id
	,a.EmailAddress
	,a.LastName
	,a.Firstname
	,a.PhoneNumber
	,a.Institution
	,a.MailingAddress
	,a.OfficeAddress
	,a.TitleOther
	,a.PhdTraineeCount
	,a.MscTraineeCount
	,a.InstitutionOther
	,a.MembershipReasonOther
	,a.RaceOther
	,a.MailingAddressLine1
	,a.MailingAddressLine2
	,a.MailingAddressCity
	,a.MailingAddressState
	,a.MailingAddressZip
	,a.CommunityOrganization
	,d.Name as [Degree 1]
	,ari.ResearchInterestId as RchChildId--child Rch id
	,mr.Name as [Membership Reason 1]
	,msr.Name as [Membership Service Requirement 1]
INTO NewApplication
	FROM dbo.Applications a 
		INNER JOIN dbo.ApplicationDegrees ad ON a.ID = ad.ApplicationId
		INNER JOIN dbo.ApplicationMembershipReasons amr ON a.ID = amr.ApplicationId
		INNER JOIN dbo.ApplicationMembershipServiceRequirements amsr ON a.Id = amsr.ApplicationId
		INNER JOIN dbo.ApplicationResearchInterests ari ON a.Id = ari.ApplicationId
		INNER JOIN dbo.Degrees d ON ad.ApplicationId = d.Id
		INNER JOIN dbo.MembershipReasons mr ON amr.MembershipReasonId = mr.Id
		INNER JOIN dbo.ResearchInterests ri ON ari.ResearchInterestId = ri.Id -- you don't actaully call any info from ResearchInterests so this may be unncessary
		INNER JOIN dbo.MembershipServiceRequirements msr ON amsr.MembershipServiceRequirementId = msr.Id
		INNER JOIN 
			(SELECT MIN([a.Id]) AS ID
				, a.EmailAddress 
				FROM dbo.Applications a 
				GROUP BY a.EmailAddress) AS B
		ON B.ID = a.Id  --filter our duplicate email address record, get the lowest id

Open in new window

Avatar of lapucca
lapucca

ASKER

Hi jfmador,
Wow, now that is magic!  I did your later suggestion and move the dbo.application right before the inner join (minus the app ) and it worked without any error message.  Now I'm wondering why?  It's the same code just minor position change.  
Avatar of jfmador
jfmador
Flag of Canada image
I'm glad it worked for you

As I mentionned earlier I suggest you to use INNER JOIN instead of defining all your table and doing the join using a where statement. You can take a look to aognenoff query he change your query to use inner join.

When you mixed cross join and inner join the table that are using inner join need to be one after the other, you cannot have cross join table between the two table this is what cause you the problem.
Avatar of lapucca
lapucca

ASKER

I see. thank you and everyone's help. I'm actually not done and will be posting more quesiton.  I just ran the stored procedue for 10 minutes and the NewApplicaiton tabel is created but no data and it won't let me drop the table either.  I'll have to post more question about this.  Thank you again.
Avatar of aognenoff
aognenoff
Flag of United States of America image
Did you switch the select query to use the inner joins?  I would think that it would take a lot less time than 10 minutes by doing that. Cross joins on all those tables are generating huge amounts of records internally which could take a really long time depending on how many records are in each of your tables.  Think of cross joins as taking # of records in table A multiplied by # of records in table B multiplied by # of records in table C and so on...with the number of tables you had in that cross join I would imagine it would run pretty slowly.

Also, for debugging you may want to run this query in parts.  So take out the INTO NewApplication part of the select query and just run the SELECT statement by itself while you're testing.  Massage the query to get the result set you expect then add it back into your stored procedure and then add the rest of the code. Breaking the problem into smaller parts makes it a ton easier to figure out whats not working.