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.
Author |
Topic |
sadbjp
INNER JOIN
41 Posts |
Posted - 2007-05-14 : 13:10:49
|
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo/*************************************************************************************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.TargetParameters: @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) ASBEGIN -- 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 ENDI have the above created stored proc. which gives the following errors:Msg 107, Level 16, State 3, Procedure usp_RubricReportGet, Line 69The 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 73The 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 78The 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.aspCODO 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 |
|
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 MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|