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 intAS 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, LastUpdatedByFROM (SELECT [ProjectResource].[Id], [Projects].[AtrackProjectId], [Projects].[Id], [ProjectResource].LastUpdated, [ProjectResource].LastUpdatedByAS [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)) aORDER 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, LastUpdatedByFROM (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)) aORDER 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)ASSET NOCOUNT ONSELECT TOP 1 CONVERT(VARCHAR(50), pr.LastUpdated, 100) AS LastUpdated, pr.LastUpdatedByFROM ProjectResource AS prINNER JOIN Projects AS p ON p.Id = pr.ProjectIdINNER JOIN Resources AS r ON r.Id = pr.ResourceIdWHERE pr.ResourceId = @ResourceId AND pr.Year = @YearORDER 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! |
 |
|
|
|
|