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.
Go to Top of Page

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'.
Go to Top of Page

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.
Go to Top of Page

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"
Go to Top of Page

ilana917
Starting Member

3 Posts

Posted - 2008-07-15 : 15:49:35
Thanks, I think it's working now, I appreciate the help!
Go to Top of Page
   

- Advertisement -