Posted by Tibor Karaszi on 02/19/07 21:31

You are missing naming the derived table. Note the AS tbl at the end of the inner query:

SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


<bbla32@op.pl> wrote in message news:1171920105.335960.271700@m58g2000cwm.googlegroups.com...
> I'll show you my query which doesn't want to compile for some reason
> (Incorrect syntax near the keyword GROUP):
>
> DECLARE @pp INT
> SET @pp = 7
>
>
> SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>
> FROM (
>
> SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.SenderID = @pp
>
> UNION
>
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverPersonID = @pp
>
> UNION
>
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
> AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @pp AND PS.ObsoleteDate IS NULL)
>
> UNION
>
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
> AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @pp)
> )
>
> )
>
> GROUP BY [ID]
>

[Back to original message]