Errors in code - SQL Server Forums

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

Errors in code

Author

Topic

sadbjp
INNER JOIN

41 Posts

Posted - 2007-05-14 : 13:10:49

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*************************************************************************************
Description: This store procedure is used to return the district's Rubric Report.
Returns:
RubricReport.ReportID
RubricReport.LastUpdate
RubricReport.LastUpdateBy
RubricReportDetail.LocalPerf
RubricReportDetail.GoalMet
RubricReportTemplate.IndicatorNumber
RubricReportTemplate.Topic
RubricReportTemplate.Part
RubricReportTemplate.ILCDComponent
SppTarget.Target
Parameters:
@DataYears
@County
@District
@LastUpdateBy

*/
ALTER PROCEDURE [dbo].[usp_RubricReportGet]
-- Add the parameters for the stored procedure here
@DataYears CHAR(8),
@County CHAR(2),
@District CHAR(4),
@LastUpdateby VARCHAR(50),
@ReportID INT,
@LastUpdate SMALLDATETIME,
@IndicatorID TINYINT,
@IndicatorNumber VARCHAR(5),
@Number VARCHAR(20),
@FYY CHAR(9),
@Part CHAR(1),
@Years CHAR(8),
@maxLastUpdate SMALLDATETIME,
@LocalPerf DECIMAL(5,4),
@GoalMet BIT,
@Topic VARCHAR(100),
@ILCDComponent VARCHAR(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

SET NOCOUNT ON;

-- Get the ReportID and LastUpdate values from Rubric Report Table matching
-- County=@County and District=@District
SELECT ReportID, LastUpdate
FROM RubricReport
WHERE (RubricReport.County = @County) AND (RubricReport.District = @District) AND (RubricReport.DataYears = @DataYears)

-- With table SPPIndicator and SppIndicator11Data, find the maximum value of LastUpdate (@maxLastUpdate)
SELECT @maxLastUpdate FROM (SELECT LastUpdate FROM SPPIndicator UNION ALL SELECT LastUpdate FROM SppIndicator11) AS D

IF @maxLastUpdate > (SELECT LastUpdate FROM RubricReport WHERE (RubricReport.County = @County) AND (RubricReport.District = @District) AND (RubricReport.DataYears = @DataYears))
BEGIN
EXEC usp_RubricReportCalc @DataYears, @County, @District, @LastUpdateBy
END

ELSE

SELECT ReportID, LastUpdate, LastUpdateBy
FROM RubricReport
WHERE RubricReport.ReportID = RubricReportDetail.ReportID

SELECT IndicatorNumber, Topic, Part, ILCDComponent
FROM RubricReportTemplate
WHERE RubricReportDetail.IndicatorID = RubricReportTemplate.IndicatorID

SELECT LocalPerf, GoalMet
FROM RubricReportDetail
WHERE SppTarget.IndicatorNumber = RubricReportDetail.IndicatorID

SELECT Target
FROM SppTarget
WHERE SppTarget.Years = @DataYears

END

I have the above created stored proc. which gives the following errors:

Msg 107, Level 16, State 3, Procedure usp_RubricReportGet, Line 69
The column prefix 'RubricReportDetail' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 3, Procedure usp_RubricReportGet, Line 73
The column prefix 'RubricReportDetail' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 3, Procedure usp_RubricReportGet, Line 78
The column prefix 'SppTarget' does not match with a table name or alias name used in the query.

How can I resolve these erros?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-14 : 13:25:55

Add the missing tables to your queries.

CODO ERGO SUM

sadbjp
INNER JOIN

41 Posts

Posted - 2007-05-14 : 13:32:32

How can I do that? Can you kindly show me that?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-14 : 13:37:43

quote:
Originally posted by sadbjp

How can I do that? Can you kindly show me that?

You don't know how to add a table to a SELECT?

If you don't know how to do that, you need to learn SQL. You can start with one of these online courses:
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

CODO ERGO SUM

sadbjp
INNER JOIN

41 Posts

Posted - 2007-05-14 : 13:39:33

I mean in which stmt. is the table missing.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-14 : 14:36:04

quote:
Originally posted by sadbjp

I mean in which stmt. is the table missing.

The tables are missing from the statements that are giving you errors.

CODO ERGO SUM

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-14 : 22:40:35

I'll take a wild guess that the tables are missing from lines 69, 73, and oh, let's say, 78.

Just a hunch.

e4 d5 xd5 Nf6

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-14 : 23:37:12

to do these queries properly, you'll have to use inner joins I'm afraid. which is too bad for you since you hate them.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83479

www.elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-15 : 04:36:53

quote:
Originally posted by jezemine

to do these queries properly, you'll have to use inner joins I'm afraid. which is too bad for you since you hate them.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83479

www.elsasoft.org

Now he/she has got new Custom Title

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources