Nested SELECT Syntax error 1

[ronspree]

I'm using SQL Server 7, and have something like this in a stored procedure (this is a simplified version):

SELECT EmployeeID, Lastname FROM (SELECT Employees.ID AS EmployeeID, Employees.Lastname FROM Employees)

The error is: Line 7: Incorrect syntax near ')'.

Strangely, the following will work:

SELECT EmployeeID, Lastname FROM (SELECT Employees.ID AS EmployeeID, Employees.Lastname FROM Employees) GO

But the following will not:

SELECT EmployeeID, Lastname FROM (SELECT Employees.ID AS EmployeeID, Employees.Lastname FROM Employees) 
GO

Strange. Anyway, I must be missing something here. What is the correct syntax for my case?

 

[jbenson001]

What is it you are trying to accomplish here? This syntax would be incorrect unless you use an IN statment. But it is unclear what you are trying to do.

Jim

 

[ronspree]

Its something like:

Create Procedure strDatasheet
@PeriodID int
AS
SELECT EmployeeID, SUM(WorkEarnings)
FROM
(SELECT EmployeeID, Rate * Quantity AS WorkEarnings From WeeklyEarnings WHERE PeriodID = @PeriodID
UNION ALL
SELECT EmployeeID, Amount AS WorkEarnings FROM OtherEarnings
WHERE PeriodID = @PeriodID)
GROUP BY EmployeeID

The problem with the inner Select statement is that multiple instances of EmployeeID are present because of the UNION ALL. So to group EmployeeID, I nested it inside another select with the GROUP BY clause.

 

[swampBoogie]

Derived tables must be have a name

select <...>
 from (select ... ) dt

In your second example GO was interpreted as a name for the derived table.

 

[ronspree]

Swamp, so that's what its called: Derived tables. Searching for "derived tables" on the BOL confirmed your answer: derived tables should have a name. Thank you very much. My code should have been:

SELECT EmployeeID, Lastname FROM (SELECT Employees.ID AS EmployeeID, Employees.Lastname FROM Employees) AS DerivedEmployeesTable