Solved

Need help with SQL syntax error

Posted on 2008-10-16
17
291 Views
Last Modified: 2010-03-20
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

0
Comment
Question by:lapucca
  • 6
  • 6
  • 3
  • +2
17 Comments
 

Expert Comment

by:JDEE8297
ID: 22737447
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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22737463
Line 50 should be:

inner join (SELECT min([Id]) AS ID, [email] from [dbo].[Applications] group by [EmailAddress]) AS B
 
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22737464
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
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:lapucca
ID: 22737473
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

0
 
LVL 5

Expert Comment

by:jfmador
ID: 22737492
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

0
 
LVL 5

Expert Comment

by:jfmador
ID: 22737513
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



0
 

Author Comment

by:lapucca
ID: 22737515
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

0
 
LVL 1

Expert Comment

by:aognenoff
ID: 22737517
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.
0
 

Author Comment

by:lapucca
ID: 22737530
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
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22737531
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
0
 

Author Comment

by:lapucca
ID: 22737544
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.
0
 
LVL 5

Accepted Solution

by:
jfmador earned 500 total points
ID: 22737561
Ok I think that all the cross join and the inner join causes the problem

I will suggest you to add inner join for each table instead of defining your table and using a where.

Or move your dbo.applications near from your inner join  (i prefer the first option) but this one should work

 FROM dbo.ApplicationDegrees,
dbo.[Degrees] as tblDegrees, dbo.ApplicationMembershipReasons, dbo.ApplicationMembershipServiceRequirements,
dbo.ApplicationResearchInterests, dbo.ResearchInterests as tblRschInt, dbo.MembershipReasons,
dbo.MembershipServiceRequirements,
dbo.Applications app
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
0
 
LVL 1

Expert Comment

by:aognenoff
ID: 22737582
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

0
 

Author Comment

by:lapucca
ID: 22737606
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.  
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22737623
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.
0
 

Author Comment

by:lapucca
ID: 22737649
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.
0
 
LVL 1

Expert Comment

by:aognenoff
ID: 22737668
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.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now