SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Errors in code
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sadbjp
INNER JOIN

41 Posts

Posted - 05/14/2007 :  13:10:49  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 05/14/2007 :  13:25:55  Show Profile  Reply with Quote
Add the missing tables to your queries.




CODO ERGO SUM
Go to Top of Page

sadbjp
INNER JOIN

41 Posts

Posted - 05/14/2007 :  13:32:32  Show Profile  Reply with Quote
How can I do that? Can you kindly show me that?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/14/2007 :  13:37:43  Show Profile  Reply with Quote
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
Go to Top of Page

sadbjp
INNER JOIN

41 Posts

Posted - 05/14/2007 :  13:39:33  Show Profile  Reply with Quote
I mean in which stmt. is the table missing.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/14/2007 :  14:36:04  Show Profile  Reply with Quote
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
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 05/14/2007 :  22:40:35  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/14/2007 :  23:37:12  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 05/15/2007 :  04:36:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000