Solved

Need help with SQL syntax error

Posted on 2008-10-16
17
287 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

16 Experts available now in Live!

Get 1:1 Help Now