sub query syntax - 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

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

sub query syntax

Author

Topic

ilana917
Starting Member

3 Posts

Posted - 2008-07-15 : 11:30:47

Hi,

Can someone tell me what might be wrong with this statement? The error I keep getting is: "Incorrect syntax near the word 'order'." Thanks!

ALTER PROCEDURE [dbo].aspnet_GetLastUpdatedRecord

@ResourceId int,
@Year int

AS
SELECT TOP 1 CONVERT(varchar(50), [ProjectResource].LastUpdated, 100) AS LastUpdated, LastUpdatedBy
FROM
(
SELECT [ProjectResource].[Id], [Projects].[AtrackProjectId], [Projects].[Id], [ProjectResource].LastUpdated, [ProjectResource].LastUpdatedBy
AS [AtrackRequestId], [ProjectResource].[Year], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12], [ProjectResource].[ApplicationId]
FROM [ProjectResource], [Projects], [Resources]
WHERE ([Projects].[Id] = [ProjectResource].[ProjectId]) AND ([Resources].[Id] = [ProjectResource].[ResourceId]) AND ([Resources].[Id] = @ResourceId) AND ([ProjectResource].[Year] = @Year)
)
ORDER BY LastUpdated DESC
RETURN

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-15 : 11:42:07

Try the 2 changes in red below...

SELECT TOP 1 CONVERT(varchar(50), ~~[ProjectResource].~~LastUpdated, 100) AS LastUpdated, LastUpdatedBy
FROM
(
SELECT [ProjectResource].[Id], [Projects].[AtrackProjectId], [Projects].[Id], [ProjectResource].LastUpdated, [ProjectResource].LastUpdatedBy
AS [AtrackRequestId], [ProjectResource].[Year], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12], [ProjectResource].[ApplicationId]
FROM [ProjectResource], [Projects], [Resources]
WHERE ([Projects].[Id] = [ProjectResource].[ProjectId]) AND ([Resources].[Id] = [ProjectResource].[ResourceId]) AND ([Resources].[Id] = @ResourceId) AND ([ProjectResource].[Year] = @Year)
) a
ORDER BY LastUpdated DESC

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

ilana917
Starting Member

3 Posts

Posted - 2008-07-15 : 11:47:54

Hmm thanks, I'm getting a different error instead now:

"The column Id was specified multiple times for 'a'. Invalid column name 'LastUpdatedBy'.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-15 : 12:04:00

That's because your subquery has some columns with the same names. You can get rid of the columns you don't need from the subquery...

SELECT TOP 1 CONVERT(varchar(50), LastUpdated, 100) AS LastUpdated, LastUpdatedBy
FROM 
(
SELECT [ProjectResource].[Id], [Projects].[AtrackProjectId], [Projects].[Id], [ProjectResource].LastUpdated, [ProjectResource].LastUpdatedBy 
AS [AtrackRequestId], [ProjectResource].[Year], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12], [ProjectResource].[ApplicationId] 
FROM [ProjectResource], [Projects], [Resources] 
WHERE ([Projects].[Id] = [ProjectResource].[ProjectId]) AND ([Resources].[Id] = [ProjectResource].[ResourceId]) AND ([Resources].[Id] = @ResourceId) AND ([ProjectResource].[Year] = @Year)
) a
ORDER BY LastUpdated DESC

There's more you can do, but let's do it step-by-step.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 13:38:53

How about some ANSI joins too?

ALTER PROCEDURE dbo.aspnet_GetLastUpdatedRecord
(
	@ResourceId int,
	@Year int
)
AS

SET NOCOUNT ON

SELECT TOP 1	CONVERT(VARCHAR(50), pr.LastUpdated, 100) AS LastUpdated,
		pr.LastUpdatedBy
FROM		ProjectResource AS pr
INNER JOIN	Projects AS p ON p.Id = pr.ProjectId
INNER JOIN	Resources AS r ON r.Id = pr.ResourceId
WHERE		pr.ResourceId = @ResourceId
		AND pr.Year = @Year
ORDER BY	pr.LastUpdated DESC

E 12°55'05.25"
N 56°04'39.16"

ilana917
Starting Member

3 Posts

Posted - 2008-07-15 : 15:49:35

Thanks, I think it's working now, I appreciate the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources